SQL Formatter – Format Options in SQL

Formatting 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:

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

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) :

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);

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:

SELECT * FROM Smoothie_Tbl
WHERE PRICE < 4.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.