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 'Hello' + ' ' + 'World' AS greet;
This should combine the provided string values as follows:
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:
Output:
The previous example adds a space character at the beginning of the string.
Output:
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:
Output:
The previous example converts the input string to uppercase.
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:
Output:
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!
