Db2- How to Check If Varchar Field Value Has Integers

DB2- How to check if varchar field value has integers

Doc Link

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

Test for numeric value?

A fairly reliable (but somewhat hackish) way is to compare the string to its upper- and lower-case self (numbers don't have different cases). As long as your data that is bringing in characters only includes Latin characters, you should be fine:

SELECT input, CASE
    WHEN UPPER(input) = LOWER(input) THEN TO_NUMBER(input)
    ELSE 0
END AS output
FROM source

Another option would be to use the TRANSLATE function:

SELECT input,
    CASE 
        WHEN TRANSLATE(CAST(input as CHAR(10)), '~~~~~~~~~~~~~', '0123456789-. ') = '~~~~~~~~~~' THEN CAST(input AS DECIMAL(12, 2))
        ELSE 0
    END AS num
FROM x

Check if character column contains numeric value in DB/2 with sql

Finally got a working solution although it may not be the best:

 SELECT        *
 FROM           tab e, tab n
 WHERE        (e.LN = 0001) AND  (n.RMK LIKE CONCAT(RTRIM(CONCAT('%', CHAR(e.ORDNO))), '%'))

There was whitespace being added to the end of the e.ORDNO string for some reason using CHAR and there was whitespace at the end of the RMK string too; and the wildcard search wouldn't work unless I prepended and appended the '%' to e.ORDNO or I did a right trim on both n.RMK and e.ordno. Not sure if one way is better than the other.

Check if a string contains only number

I would suggest try_convert():

select (case when try_convert(col, float) is not null then 'valid' else 'invalid' end)

The one possible downside is exponential format; 1e6 is a valid number for instance.

An alternative is the where approach; you just need more complete logic:

select (case when col like '%[^0-9.]%' then 'invalid'
             when col like '%.%.%' then 'invalid'
             else 'valid'
        end)

SQL get numeric rows from text column

Assuming the field is 10 digits long (like mine) this would be the solution.

WHERE
  (  
     SUBSTRING(ID_NUM,1,1,OCTETS) BETWEEN '0' AND '9'
  OR SUBSTRING(ID_NUM,2,1,OCTETS) BETWEEN '0' AND '9'
  OR SUBSTRING(ID_NUM,3,1,OCTETS) BETWEEN '0' AND '9'
  OR SUBSTRING(ID_NUM,4,1,OCTETS) BETWEEN '0' AND '9'
  OR SUBSTRING(ID_NUM,5,1,OCTETS) BETWEEN '0' AND '9'
  OR SUBSTRING(ID_NUM,6,1,OCTETS) BETWEEN '0' AND '9'
  OR SUBSTRING(ID_NUM,7,1,OCTETS) BETWEEN '0' AND '9'
  OR SUBSTRING(ID_NUM,8,1,OCTETS) BETWEEN '0' AND '9'
  OR SUBSTRING(ID_NUM,9,1,OCTETS) BETWEEN '0' AND '9'
  OR SUBSTRING(ID_NUM,10,1,OCTETS)BETWEEN '0' AND '9'
  )

How do I check to see if a value is an integer in MySQL?

I'll assume you want to check a string value. One nice way is the REGEXP operator, matching the string to a regular expression. Simply do

select field from table where field REGEXP '^-?[0-9]+$';

this is reasonably fast. If your field is numeric, just test for

ceil(field) = field

instead.

MySQL - Select only numeric values from varchar column

SELECT * 
FROM mixedvalues 
WHERE value REGEXP '^[0-9]+$';

---

Related Topics