SQL Standard

SQL String Formatting

Strings are a fundamental data type in any programming language but it specifically plays an important role in databases. Whether we are talking about char, varchar, or similar data type, strings are the base on how we store the text data in databases.

Like any other programming language, we may encounter such instances where we need to format the string values into various formats. This can be data manipulation or creating reports from the stored data.

String formatting basically refers to a step of controlling the appearance and structure of the text and numeric values within queries and results.

In this tutorial, we will explore the various methods that we can use to perform the string formatting across SQL databases. It is good to keep in mind that different databases have varying formatting options. Although we will try to explore the most common for each database, we cannot exhaust all of them in this post.

However, we have dedicated tutorials for string formatting for MySQL, PostgreSQL, SQL Server, Oracle, and Standard SQL. Check them out from our search page to learn more.

String Formatting

Before we dive in, it is good to note that the string formatting can take different presentations. For example, it can mean changing the casing, concatenating the strings, removing the whitespaces, formatting the numbers, displaying the date and timestamp values in human-readable format, and more.

It is therefore needed to first define what type of formatting you are aiming for before creating the queries for the tasks.

String Concatenation

Let us start with the most basic and common string formatting task which is string concatenation.

String concatenation refers to the process of combining multiple string values into a single string.

Depending on the target database engine, we can use the concat() function or the “+” operator to perform the string concatenation in SQL.

An example is as follows:

SELECT CONCAT('Hello', ' ', 'World') AS greet;
SELECT 'Hello' + ' ' + 'World' AS greet;

 
This should combine the provided string values as follows:

Hello World

 

String Padding

The second common string operation in SQL is string padding. Padding refers to the process of adding the characters such as whitespace characters or zeroes to reach a specific length.

Padding is useful when aligning the text or formatting the numeric values.

In SQL, we have access (database engine dependent) to the LPAD() and RPAD() functions which allows us to pad the characters to the left or right of the string, respectively.

An example is as follows:

SELECT LPAD('123', 5, ' ') AS str;

 
Output:

'  123'

 
The previous example adds a space character at the beginning of the string.

SELECT RPAD('123', 5, ' ') AS str;

 
Output:

'123  '

 
This should add a space at the end of the string.

You can replace the space character with any other supported character.

String Casing

Another common string formatting operation is changing the casing of a given string. We can convert a string into lowercase or uppercase using the UPPER() and LOWER() functions.

An example is as follows:

SELECT UPPER('hello') AS str;

 
Output:

--        'HELLO'

 
The previous example converts the input string to uppercase.

SELECT LOWER('WORLD') AS str;

 
The same case applies to lowercasing.

Date and Time Formatting

When working with date and time values, you may come across instances where we need to format the values into a human-readable format.

Unfortunately, the date and time formatting methods will vary from one database engine to another engine. However, in MySQL, we can use the DATE_FORMAT() function as shown in the following example:

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS date;

 
Output:

--        '2024-01-14 12:34:56'

 
This should return the current date in a human-readable format.

You can replace the placeholder values such as Y, m, d, etc. to represent which unit of value you wish to show.

Conclusion

In this tutorial, we learned about the various string formatting operations that we can perform in SQL databases. This includes the string concatenation, padding, and more. We recommend you to check out our dedicated tutorials for all the other operations for all the database engine. You will not be disappointed!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list