DB2: DIGITS and SUBSTR Functions

Built-in functions in DB2 SQL are useful for SQL query tuning. Here is my other post on built-in functions.

The DIGITS function is similar to the CHAR function. This function converts a numeric value to a character string value.

Below example returns a string, which is unsigned, meaning that it is based on the absolute value of the numeric value supplied. Also it will exclude decimal portion.

Example:

SELECT DIGITS(-10123.858)
FROM SYSIBM.SYSDUMMY1

The result is it returns a value of 10123858. When you need a portion of substring (numeric) from the main string, you can use this function.

For example, the following statement will take a date stored in an 8-digit numeric field as 20041231 and convert it to a displayable string representation of the date in mm/dd/yyyy format.

SELECT SUBSTR(DIGITS(DTEFLD),5,2) || ‘/’ ||
SUBSTR(DIGITS(DTEFLD),7,2) || ‘/’ ||
SUBSTR(DIGITS(DTEFLD),1,4)
FROM MYTABLE

Assuming that the value of DTEFLD is 20041231. It returns, when the above statement is executed, a value of ‘12/31/2004’.

Note: You can use “Digits” function to convert “Binary” to “Char” format.

Here are DB2 Built-in functions. When you use these correctly, your SQL queries take less CPU time.

Related Posts