Following is the list of commonly used Text-Manipulation Functions:
| Function | Description | Example |
|---|---|---|
| LEFT() | Returns characters from left of string | LEFT(cust_firstname, 4) |
| LENGTH() | Returns the actual length of a string | LENGTH(cust_firstname) |
| LOWER() | Converts string to lowercase | LOWER(cust_firstname) |
| LTRIM() | Trims white space from left of string | LTRIM(cust_firstname) |
| RIGHT() | Returns characters from right of string | RIGHT(cust_firstname) |
| RTRIM() | Trims white space from right of string | RTRIM(cust_firstname) |
| UPPER() | Converts string to uppercase | UPPER(cust_firstname) |
| SUBSTR() | Returns a substring of a string. 2nd argument – starting position 3rd argument – length | SUBSTR(cust_firstname,3,4) |
| HEX() | Returns the hexadecimal representation of its argument | HEX(cust_firstname) |
DB2 SQL LEFT function returns the leftmost integer characters of the string.
SYNTAX : LEFT(String, length)
SELECT LEFT(Employeename, 4)
FROM TB_Employee; |
Assume the Employeename (VARCHAR(20)) has a value of 'TWIN BROTHERS'. So the first 4 character "TWIN" will be displayed.
DB2 SQL SUBSTR function returns a substring of a string.
SYNTAX : SUBSTR(String, start, length)
SELECT SUBSTR(Employeename, 1, 4)
FROM TB_Employee; |
Assume the Employeename (VARCHAR(20)) has a value of 'TWIN BROTHERS'. So the first 4 character "TWIN" will be displayed.
Note: if staring position is not provided(i.e only one number is present in SUBSTR function). It will extract from column 1.
SELECT SUBSTR(Employeename, 4)
FROM TB_Employee; |
Returns the value 'TWIN'. Result same as first query.
DB2 SQL LENGTH function returns the length of a value.
SYNTAX : LENGTH(String)
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is the length of the argument. The length of strings includes blanks. The length of a varying-length string is the actual length, not the length attribute.
SELECT LENGTH(Employeename)
FROM TB_Employee; |
Assume the Employeename is a varying-length character string with a value of 'TWIN BROTHERS'.
The above SQL query returns the value 13.
DB2 SQL LTRIM function removes any of the specified characters from the beginning of an expression.
SYNTAX : LTRIM(String, trim_string)
The LTRIM function removes all of the characters that are contained in trim-string from the beginning of string. If trim-string is not specified only blanks are removed.
SELECT LTRIM(Employeename)
FROM TB_Employee; |
Assume the Employeename is a defined as CHAR(20) and has a value of ' TWIN BROTHERS'.
The above SQL query returns the value 'TWIN BROTHERS'.
Let us see another example in LTRIM.
Use the LTRIM function to remove individual numbers in the trim-string from the beginning (left side) of the string.
SELECT LTRIM ('123DEFG123', '321'),
LTRIM ('12DEFG123', '321'),
LTRIM ('123123222XYZ22', '123'),
LTRIM ('12321', '213'),
LTRIM ('XYX123 ', '321')
FROM SYSIBM.SYSDUMMY1 |
The result of the above sql query is,
'DEFG123' 'DEFG123' 'XYZ22' '' (an empty string - all characters removed) 'XYX123' (no characters removed) |
The LTRIM function does not remove instances of '1', '2', and '3' on the right side of the string, following characters that are not '1', '2', or '3'.
If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!