DB2- How to check if varchar field value has integers

Question

Is there an inbuilt DB2 function or any query to check if the character i have is a number? (I cannot use user defined functions)

xQbert · Accepted Answer · 2022-05-18T15:54:33.803

34

Doc Link

CASE
  WHEN LENGTH(RTRIM(TRANSLATE(test_str, '*', ' 0123456789'))) = 0 
  THEN 'All digits'
  ELSE 'No'
END

edited May 18 '22 at 15:54

answered May 07 '12 at 21:58

xQbert

34,733
2
41
62

1

I get Error: [SQL0171] Argument 2 of function TRANSLATE not valid. SQLState: 42815 ErrorCode: -171 – Albert Hendriks Jul 21 '20 at 07:29
Assuming this is DB2... what version ? Also... what does your sue of translate look like? (Really this should be a new question as this is over 8 years old at this point!) – xQbert Jul 21 '20 at 15:14

score 8 · Answer 2 · edited Mar 03 '23 at 15:45

if your version of db2 can use regexp_like you can do it:

number with "." as decimal symbol:

 select *      from yourtable                            
 where REGEXP_LIKE(trim(yourzone) , '^\d+(\.\d*)?$')

number with "," as decimal symbol:

 select *      from yourtable                            
 where REGEXP_LIKE(trim(yourzone) , '^\d+(\,\d*)?$')

number without decimal symbol ( integer only, your ask)

 select *      from yourtable                  
 where REGEXP_LIKE(trim(yourzone) , '^\d+$')

score 6 · Answer 3 · answered Feb 21 '16 at 21:14

There are many approaches. Take a look at that solution using only two functions:

CASE
    WHEN REPLACE(TRANSLATE(test_str, '0','123456789','0'),'0','') = ''
    THEN 'All digits'
    ELSE 'Not all digits'
END

In general - less functions - better performance :)

score 1 · Answer 4 · answered May 07 '12 at 22:00

1

Use ASCII function to get character value and compare that it is between 48 '0' and 57 '9'

ASCII Table

ASCII Function Returns the ASCII code value of the leftmost character of the argument as an integer.

answered May 07 '12 at 22:00

Atilla Ozgur

14,339
3
49
69

This will work only if the string op has is one character length – Lamak May 07 '12 at 22:02
1

Why not just say "where c between '0' and '9'"? – Gordon Linoff May 07 '12 at 22:03
he asks "if the character i have is a number?" – Atilla Ozgur May 07 '12 at 22:15

score 0 · Answer 5 · answered Dec 03 '15 at 07:19

0

The answer by xQbert is not completely correct. What you actually need is a * for every character in the fromString (and the space needs to be removed) and the length of the to string needs to be the same as the length of the original string.

so it will look like this:

CASE
  WHEN LENGTH(RTRIM(TRANSLATE(test_str, '**********', '0123456789'))) = LENGTH(RTRIM(test_str)) 
  THEN 'All digits'
  ELSE 'No'
END

answered Dec 03 '15 at 07:19

Ruben van Straten

9
4

1

The xQbert answer is completely correct; it just doesn't include an explanation. A "*" is not needed for `every character`. Only a single "*" is used specifically because it would TRANSLATE() **only** the leading space of the _from-string_ while all digits would become spaces. Other characters are unchanged. If only digits are there, they'll all be blank; and LENGTH(TRIM()) becomes zero. – user2338816 Feb 22 '16 at 02:35

score 0 · Answer 6 · edited Jan 15 '16 at 13:37

Returns numeric where char field is all numerics with no leading or trailing spaces. ie; All characters in the field are numeric:

where translate(char_field, 'X         ',' 0123456789') = ' '

Returns non-numeric values with leading spaces considered non-numeric, but trailing spaces ignored. ie; non-numeric if there are leading spaces, but not if there are trailing spaces. This is a common occurrence for mainframe/Cobol-loaded fields:

where not ( length(rtrim(translate(substr(char_field,1,length(rtrim(char_field))),'         ','0123456789'))) = 0)

Returns numeric with trailing, but not leading spaces after value. ie; Leading spaces are treated as non-numeric, but trailing spaces are ignored. Again, common for mainframe/Cobol CHAR fields:

where ( length(rtrim(translate(substr(char_field,1,length(rtrim(char_field))),'X         ',' 0123456789'))) = 0)

Returns numeric with leading & trailing spaces. ie; ignores leading and trailing spaces in determining field is "numeric":

where ( length(ltrim(rtrim(translate(substr(char_field,1,length(ltrim(rtrim(char_field)))),'         ','0123456789')))) = 0)

score 0 · Answer 7 · answered Mar 24 '16 at 22:47

I have made more error-prone version based on the idea xQbert exposed, added intermedia result, some examples and to_integer column which converts string value safely to integer:

select 
      test_str
    ,                  TRIM(TRANSLATE(replace(trim(test_str), ' ', 'x'), '           ', '0123456789'))
    , case when length(TRIM(TRANSLATE(replace(trim(test_str), ' ', 'x'), '           ', '0123456789')))=0 
      then cast(test_str as int) else null end to_integer
    , case when length(TRIM(TRANSLATE(replace(trim(test_str), ' ', 'x'), '           ', '0123456789')))=0 
      then 'integer'  else 'not integer' end is_integer
from (VALUES 
        ('  123  '  )
        ,('  abc  ' )
        ,('  a12  ' )
        ,('  12 3  ')
        ,('  99.3  ')
        ,('993'     )
    ) AS X(test_str)
;

The result for this example set is:

TEST_STR 2        TO_INTEGER  IS_INTEGER
-------- -------- ----------- -----------
  123                     123 integer
  abc    abc                - not integer
  a12    a                  - not integer
  12 3   x                  - not integer
  99.3   .                  - not integer
  993                     993 integer

You left an empty string and strings containing only spaces, where the xQbert function doesn't work (it says if all nonspace characters are digits, but strings without nonspage caracters are all digits) — Lucio Menci, Jul 14 '20 at 14:24

DB2- How to check if varchar field value has integers

7 Answers7

Linked