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.
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) || ‘/’ ||
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.