The DB2 SUBSTR function returns a substring of a string. The SUBSTR function takes two arguments (source and start location) with an optional third argument (length).
The syntax for the SUBSTR function is shown here:
-- With Required Parameters
SELECT SUBSTR({field_name}, {start_location});
-- With Optional {string_length} Parameter
SELECT SUBSTR({field_name}, {start_location}, {string_length});
To show an example statement in use:
SELECT SUBSTR(NAME, 5) AS NAME_SUBSTR FROM CATENTDESC;
The above query will take and substring the NAME column in the CATENTDESC table starting with the 5th character, and returning a resulting substring the length of which is defined by the remaining length of the column or source string.
To limit and define the length of the substring returned we can add in the length parameter to the SUBSTR function.
SELECT SUBSTR(NAME, 5, 15) AS NAME_SUBSTR FROM CATENTDESC;
The above query will take and substring the NAME column in the CATENTDESC table starting with the 5th character, and returning a resulting substring that is 15 characters in length.
NOTE: There are a few important considerations to make regarding the DB2 SUBSTR function:
- The start location must be an integer between 1 and the length or maximum length of the string, depending upon whether the string is fixed or varying-length. If the start location is out of range it will return a SQLSTATE 22011.
- If a length is explicitly specified, the source string is effectively padded on the right with the necessary number of blank characters so that the specified substring of string always exists.
