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)

Parameter Name	Description
LEADING	OPTIONAL. Removes the “trim_character” from the front or left side of the string.
TRAILING	OPTIONAL. Removes the “trim_character” from the end or right side of the string.
BOTH	OPTIONAL. Removes the “trim_character” from both(left and right) sides of the string.
Trim_Character	OPTIONAL. The character that will be removed from the given input string. If this argument is omitted, it will remove whitespace characters from a string.
String	Required. The input string to trim by the TRIM() function.

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

INPUT	OUTPUT
TRIM(‘     Tech Agilist Blog’)	Tech Agilist Blog
TRIM(‘Tech Agilist Blog    ‘)	Tech Agilist Blog
TRIM(‘     Tech Agilist Blog     ‘)	Tech Agilist Blog
TRIM(LEADING ‘ ‘ FROM ‘     Tech Agilist Blog     ‘ OR LTRIM(‘     Tech Agilist Blog     ’)	Tech Agilist Blog
TRIM(TRAILING ‘ ‘ FROM ‘     Tech Agilist Blog     ‘) OR RTRIM(‘     Tech Agilist Blog     ’)	Tech Agilist Blog
TRIM(BOTH ‘ ‘ FROM ‘     Tech Agilist Blog    ‘)	Tech Agilist Blog
TRIM(LEADING ‘XXX’ FROM ‘XXX Tech Agilist Blog XXX’) OR LTRIM(‘XXX Tech Agilist Blog XXX’ , ‘XXX’)	Tech Agilist Blog XXX
TRIM(TRAILING ‘XXX’ FROM ‘XXX Tech Agilist Blog XXX’) OR RTRIM(‘XXX Tech Agilist Blog XXX’ , ‘XXX’)	XXX Tech Agilist Blog
TRIM(BOTH ‘XXX’ FROM ‘XXX Tech Agilist Blog XXX’)	Tech Agilist Blog

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