SQL Trim Function

Strings are instrumental, especially in databases. However, you may encounter a scenario where a string contains leading or trailing characters. This can be problematic when you need to perform various string functions.

This article will discuss how you remove any leading or trailing space characters from a string in Standard SQL.

SQL Trim Function

The trim() function is part of Standard SQL and is widely adopted by major database engines. The function allows you to remove any leading or trailing characters from a string.

We can express the function syntax as shown below:

The trim function takes two main arguments. The first argument is the string containing the whitespace characters.

The function will remove the specified characters (second parameter) in the string and look for them. If you do not explicitly specify which character to remove, the function will remove all whitespace characters set in the Unicode standard.

Example

The example shown below uses the trim function to remove all leading space characters from the specified string.

The function will remove all the space characters from the string and return the string as shown below:

You can also specify other whitespace characters such as newline, tab, etc. Consider the example shown below:

This example tells the trim function to remove any leading and trailing tab and newline characters.

If you wish to remove all whitespace characters without specifying them explicitly, you can do so as shown in the example below:

Since the character parameter is optional, the function operates normally. However, it removes all leading and trailing whitespace characters.

The function should return:

SQL Trim Function (Custom)

Various database engines such as MySQL, PostgreSQL, and SQL Server have a modification of the trim function.

The syntax is as shown below:

In this case, you need to specify the leading and trailing characters from which to remove the target string.

The only difference is the keyword which allows you to specify from which string to remove the specified characters.

To remove leading characters, we can run:

In this case, we only remove the leading characters. The above query should return:

Note that the trailing characters are preserved. This is also known as LTRIM or Left-Trim.

To remove trailing characters or RTIM, we can run a query as:

This should return:

Notice how the leading space characters are not removed.

You can also remove both using the BOTH keyword in the function arguments as shown:

This should remove both the leading and trailing characters from the string. This is also known as BTRIM.

An example output is as shown:

Conclusion

In this one, we covered how to use the trim function both in Standard SQL and other database engines. The trim function allows you to remove any leading and trailing characters from a specified string.