Mainframe : DB2 Built-in Functions

ID-100118661Built-in functions in DB2 SQL are critical to your SQL query tuning. Read 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.
  • The value returned to the string is unsigned, meaning that it is based on the absolute value of the numeric value supplied. The decimal point is also excluded from the string value.

Example:

SELECT DIGITS(-10123.858)
FROM SYSIBM.SYSDUMMY1

When executed, this statement returns a value of 10123858. This function can be useful when you need to substring portions of a numeric field. 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, when this statement is executed,
a value of ‘12/31/2004’ is returned.

Note: “Digits” can also be used to convert “Binary” into “Char” format.

There are many DB2 Built-in functions. By using proper functions you can save CPU time to execute SQL query.

Advertisements

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.