---
title: "How to Use DIGITS function to get Digits from Decimal Column Value"
description: "There are many of the occasions while writing SQL query you will come across this kind of scenario that you need in the result only digits. To achieve this explained with examples of DB2 function DIGITS. This is also called column function."
url: https://srinimf.com/2017/07/19/db2-digitis-function-example/
date: 2017-07-19
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2017/07/pexels-photo-1701202.jpeg?fit=1880%2C1253&ssl=1
categories: ["Mainframe DB2"]
tags: ["Digits in SQL", "SQL DIGITS function", "SQL tips and tricks"]
type: post
lang: en
---

# How to Use DIGITS function to get Digits from Decimal Column Value

In SQL, the DIGITS function extracts digits from a decimal value, which ignores sign, periods. It gives pure numeric digits. The shared example gives you a clear idea of how it works.

Advertisements

## The syntax for DIGITS Function.

```
DIGITS ( expression )
```

The schema is SYSIBM. The DIGITS function returns a character string representation of a number.

Related[JCL-GDG Absolute Version and Generation Number](https://srinimf.com/2013/03/07/gdg/)

The argument must be an expression that returns a value of type SMALLINT, INTEGER, BIGINT or DECIMAL.

“If the argument [can be](https://srinimf.com/2017/09/08/the-story-develop-scala-applications-to-access-hadoop-data/) null, the result can be null; if the argument is null, the result is the null value.”

The result of the function is a **fixed-length character string** representing the absolute value of the argument without regard to its scale.

The result does not include a sign or a decimal character. Instead, it consists exclusively of digits, including, if necessary, leading zeros to fill out the string. The length of the
string is:

- 5 if the argument is a small integer
- 10 if the argument is a large integer
- 19 if the argument is a big integer
- p if the argument is a decimal number with a precision of p.

### Examples:

Assume that a table called TABLEX contains an INTEGER column called INTCOL containing 10-digit numbers. List all distinct four-digit combinations of the first four digits contained in column INTCOL.

```
SELECT DISTINCT SUBSTR(DIGITS(INTCOL),1,4)
FROM TABLEX;
```

Related[DB2: DIGITS and SUBSTR Functions](https://srinimf.com/2011/11/20/db2-sql-built-in-functions/)

Assume that COLUMNX has the DECIMAL(6,2) data type and that one of its values is -6.28. Then, for this value:

```
DIGITS(COLUMNX)
```

Returns the value ‘000628’.

#### Result:

The result is a string of length six (the precision of the column) with leading zeros padding the string out to this length. Neither sign nor decimal point appears in the result.

Related[10 Killer Tips to Write PLI program Easily](https://srinimf.com/2016/03/09/10-killer-tips-to-write-zos-pli-program/)

Keep Reading

Related[DB2 v10 Changes for Timestamp](https://srinimf.com/2010/03/27/db2-timestamp/)

- [DB2: DIGITS and SUBSTR Functions](https://srinimf.com/2011/11/20/db2-sql-built-in-functions/)DateNovember 20, 2011
- [SQL ‘Aggregate’ Vs ‘DISTINCT’](https://srinimf.com/2015/10/11/db2-aggregate-function-tricky-questions/)DateOctober 11, 2015
- [DB2: CEILING Scalar Function Example](https://srinimf.com/2015/10/14/ceiling-scalar-function-in-db2-the-result-can-be-null/)DateOctober 14, 2015

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2017%2F07%2F19%2Fdb2-digitis-function-example%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2017%2F07%2Fpexels-photo-1701202.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=How%20to%20Use%20DIGITS%20function%20to%20get%20Digits%20from%20Decimal%20Column%20Value)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2017/07/19/db2-digitis-function-example/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2017/07/19/db2-digitis-function-example/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2017/07/19/db2-digitis-function-example/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2017/07/19/db2-digitis-function-example/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Use%20DIGITS%20function%20to%20get%20Digits%20from%20Decimal%20Column%20Value&body=https%3A%2F%2Fsrinimf.com%2F2017%2F07%2F19%2Fdb2-digitis-function-example%2F&share=email)
-

### Like this:

Like Loading…