SQL Formatter – Format Options in SQL

sql formatterFormatting in SQL occurs when data is converted to a version of the data that is displayed in a manner, which is more visually appealing and/or more efficiently used by a Database Management System. Text, numbers, and other characters can be formatted so that they are displayed in formats that are more uniform or more structured. Values can be formatted in SQL with the use of functions such as the Format function, the to_char (CStr in Access) function, and the to_date (CDate in Access) function. Common applications of the Format function involve values using currency and date formats. Formatting can be either explicit or implicit; if you want to explicitly apply a format, then you should directly apply a means of changing a format (e.g. via a function) along with the selected format type. While formatting of datatypes can involve processes such as those for parsing, translating, and converting; formatting can take place when one writes code while using an approach that makes code easier to read.

Datatypes

In Oracle, currency values may have a data type of Number and in Access, currency values can have a data type of Money or Currency. Data types that may be used for currency values in MySQL include the Decimal datatype. I may focus more on SQL for Oracle in this article; however, if you also want to gain practice with other database options, such as Microsoft SQL Server, then you may find an Udemy class to be of interest. In Oracle, the default format for a date, is the dd-mon-yy (day-month-year) date format. As a demonstration of applying a different format for a date value, a Select statement will be used with a formatting option for the following table:

EMPLOYEE_IDEMPLOYEE_FIRSTEMPLOYEE_LASTHIRE_DATE
123BradFoster01-JAN-2012
124KaylaWheeler01-JAN-2012
125RubyWeaver01-JAN-2014
126LeeWong01-MAY-2012
127MelanieBryant01-JAN-2012
128CarolErickson01-JAN-2012
129FrancisPearson01-MAY-2012
130TaylorCohen01-JAN-2012
131JayWeber01-JAN-2012
132LouisIngram01-JAN-2014
133SheldonCross01-JAN-2012
134SallyRiley01-JAN-2014
135MaryanneWaters01-JAN-2012
136BlakeManning01-MAY-2012

 

SELECT
Employee_Id,
Employee_First,
Employee_Last,
TO_CHAR(Hire_Date, ‘MM-DD-YYYY HH:MI AM’)
AS
Formatted_Date
FROM
Employee_Tbl;

 

EMPLOYEE_ID
EMPLOYEE_FIRST
EMPLOYEE_LAST
HIRE_DATE
FORMATTED_DATE
123
Brad
Foster
01-JAN-2012
01-01-2012  12:00AM
124
Kayla
Wheeler
01-JAN-2012
01-01-2012 12:00 AM
125
Ruby
Weaver
01-JAN-2014
01-01-2014 12:00 AM
126
Lee
Wong
01-MAY-2012
05-01-2012 12:00 AM
127
Melanie
Bryant
01-JAN-2012
01-01-2012 12:00 AM
128
Carol
Erickson
01-JAN-2012
01-01-2012 12:00 AM
129
Francis
Pearson
01-MAY-2012
05-01-2012 12:00 AM
130
Taylor
Cohen
01-JAN-2012
01-01-2012 12:00 AM
131
Jay
Weber
01-JAN-2012
01-01-2012 12:00 AM
132
Louis
Ingram
01-JAN-2014
01-01-2014 12:00 AM
133
Sheldon
Cross
01-JAN-2012
01-01-2012 12:00 AM
134
Sally
Riley
01-JAN-2014
01-01-2014 12:00 AM
135
Maryanne
Waters
01-JAN-2012
01-01-2012 12:00 AM
136
Blake
Manning
01-MAY-2012
05-01-2012 12:00 AM

Note that the time portion for the Formatted_Date column values is displayed as “12:00 AM”; In Oracle and Access, the default time is set as midnight. In the following example, you will see a demonstration of the to_date function being used within an Insert statement:

Initial Table (used for a library to record books that have been checked out) :

ACCOUNT_IDBOOK_TITLECHECKOUT_DATECHECKIN_DATE
87563Biology15-MAY-201430-MAY-2014
62841Literature: A Pocket Anthology01-MAY-201420-MAY-2014
56422Pete the Cat06-MAY-201412-MAY-2014
68442Soccer on Sunday05-MAY-201412-MAY-2014
63587Earth Science01-MAY-201430-MAY-2014
62012Software Abstractions12-MAY-201429-MAY-2014
22745Design: The Key Concepts15-MAY-201430-MAY-2014
43288Programming Introduction26-MAY-201430-MAY-2014

In this case, an Insert statement (in Oracle SQL) is used when a book is checked in; sysdate is used for the new value for the Checkin_Date column as it will retrieve the current date and time.

INSERT INTO Library_Tbl
VALUES (000068527, ‘The Power of Now’,
TO_DATE
(’05-30-2014 09:30 AM’, ‘MM-DD-YYYY HH:MM AM’),
sysdate);

 

68527The Power of Now30-MAY-201403-JUN-2014

Note that, although the time portion of the to_date function had been added, the time had not been displayed along with the date in the output. When using the to_date function, the time is still stored in tables; however, you will have to explicitly retrieve the data for the time. You can find helpful tutorials that cover concepts, such as implicit and explicit conversions, on Udemy.

Formatting and The Where Clause

A factor to be mindful of is that when a Select statement is used along with a Where clause, even if the value of a column displays a dollar sign, you should not use the dollar sign in the Select statement. An example of a Select statement being used to select a specific value in a column containing currency values is as follows:

Table:

MENU_ITEMDESCRIPTIONPRICE
1Tropical Burst$3.00
2Protein Peanut$4.00
3Java Almond$4.00
4Very Berry$3.00
5Apple Orchard$3.00

 

SELECT * FROM Smoothie_Tbl
WHERE PRICE < 4.00;

 

MENU_ITEMDESCRIPTIONPRICE
1Tropical Burst$3.00
4Very Berry$3.00
5Apple Orchard$3.00

Note that the dollar sign had been omitted when the Where clause had been used to search for all columns from the Smoothie_Tbl that contained values of less than 4.00 for the Price column.

Readability

One can apply personal standards or groups can adapt a uniform standard in order to use formatting that increases the level of readability for code. When you refer back to code that you have previously written, it may be helpful with having notations that help you recall why each line of code had been added. Additionally, if someone who had not been the author of code reads prewritten code, it may helpful for them to be able to understand the objective of the code as well as to be able to easily read the code. If code is written using a format that is not easy to read, it may still produce the intended results, but it can require more time spent deciphering the code in cases such as when a program is being updated. Some formatting techniques to use when writing code are as follows:

  • Refrain from putting all code on one line or wrapping segments of code
  • Add comments in order to provide details for the basis and purpose of lines of code
  •  Use uppercase letters for SQL keywords
  •  Indent as appropriate (e.g. when using nested statements)

Reporting

Formatting options can also be used for reporting. There are cases in which reports contain accurate data, but the formatting of the reports is not ideal. For instance; suppose that the Library_Tbl table used earlier had contained multiple instances of books being checked out for the same account number. As opposed to all of the instances being displayed along with the duplicate account number, a formatting option such as the SQL *PLUS Break command may be used as follows:

BREAK ON ACCOUNT_ID;

By using the Break command for the Library_Tbl, the output would still include all of the table data for books checked out under the same account numbers, but there would be empty spaces in the Account_Id column (after initial instances of the account numbers) where duplicate account numbers would have been displayed.

Summary

There is an abundant amount of formatting options available for each SQL version. This article has included some of the formatting options; if you are interested in discovering more formatting options, then I would like to suggest tutorials on Udemy. Formatting options can improve layouts for tables, general readability of code, as well as formats for reports. It is also important to acknowledge formats used in tables while performing operations, such as queries and updates, for databases.