DB2 TRIM – Remove any characters from STRING

The DB2 TRIM function removes bytes from the beginning, from the end, or from both the beginning and end of a string expression. This DB2 TRIM function can be used with any other character also. It is a scalar function in DB2 that is most often used with strings.

DB2 TRIM Syntax

TRIM([LEADING (L) | TRAILING (T) | BOTH (B)] Trim_Character FROM String)
RTRIM(String, Trim_Character)
LTRIM(String, Trim_Character)

The source string can have any expression from which we extract the final output and which should return whose datatype is built-in one from the list –

• GRAPHIC
• VARGRAPHIC
• CHAR
• VARCHAR
• BINARY
• VARBINARY
• Numeric
• Datetime

In case the specified value of the source string does not belong to any of the above-mentioned datatypes then it is cast to VARCHAR implicitly by the function before the trimmed value of the source string is evaluated.

It is necessary that both the datatypes of the source string and the character to be removed are compatible with each other while using the function. The data type of the output string from the function depends upon the data type of the source string expression and the dependency followed over there is as specified below –

• The resultant string is in VARCHAR if the source string has the data type of VARCHAR or CHAR.
• The data type of the output string is a VARGRAPHIC if the datatype of source string expression is GRAPHIC or VARGRAPHIC.
• It is in the VARBINARY datatype output value if the source string has VARBINARY or BINARY datatype.

Example of LEADING DB2 TRIM to remove Leading SPACE

String – ‘  New Year comes with great opportunities   ‘. This string has the leading as well as trailing spaces in it. The following statement is used to remove all the blank spaces in the beginning –

SELECT
TRIM(LEADING FROM '  New Year comes with great opportunities   ') OutputString
FROM
sysibm.sysdummy1;

OR

SELECT
LTRIM('  New Year comes with great opportunities   ') OutputString
FROM
sysibm.sysdummy1;

The output of the above example is as shown in the below image with no spaces in the beginning –

OutputString: New Year comes with great opportunities

Example of TRAILING DB2 TRIM to remove Trailing SPACE

String – ‘  New Year comes with great opportunities   ‘. This string has the leading as well as trailing spaces in it. The following statement is used to remove all the blank spaces at the end –

SELECT
TRIM(TRAILING FROM '  New Year comes with great opportunities   ') OutputString
FROM
sysibm.sysdummy1;

OR

SELECT
RTRIM('  New Year comes with great opportunities   ') OutputString
FROM
sysibm.sysdummy1;

OutputString:      New Year comes with great opportunities

Example of BOTH DB2 TRIM to remove all Leading & Trailing SPACE

String – ‘  New Year comes with great opportunities   ‘. This string has the leading as well as trailing spaces in it. The following statement is used to remove all the blank spaces from both the end –

SELECT
TRIM(BOTH FROM '  New Year comes with great opportunities  ') OutputString
FROM
sysibm.sysdummy1;

OutputString: New Year comes with great opportunities

Note: The TRIM() function will not remove any characters (whitespaces or user-defined) in between the string.

Conclusion

We can make use of the TRIM() scalar function in DB2 DBMS provided by IBM to remove a particular character or blank space from the start or end of a string. By default, if we don’t mention LEADING or TRAILING while using the function the character is stripped from both sides. Also, if the character to be removed that is strip character is not mentioned, it is considered to be blank space.

Read DB2 blogs: Click Here IBM DB2 Manual: Click Here