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.


SELECT DIGITS(-10123.858)

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.

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.

Related Posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.