CODEWITHSUNDEEP
sqlregexoracle-databaseregexp-like
PythonDeveloper
PythonDeveloper

Reputation: 404

Validate one column only has numbers using Regular Expression

I need to verify one column only has numbers(integer number only) in oracle . If yes then set flag as 1 otherwise 0. I am trying to use below reg_exp( but it sets flag to 1 even for hyphen ):

CASE WHEN REGEXP_LIKE(Column_name,'\d\) THEN 1 ELSE 0 END

Examples: 12345 should ( this is working and above code is setting flag to 1) -1234 should set flag to 0 ( above code is setting it to 1) abcd should set flag to 0 ( above code is working correct in this case) 123.23 should set flag to 0

Upvotes: 0

Views: 413

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You may use TRANSLATE function

SELECT case when TRANSLATE(Column_name, 'X0123456789', 'X') is null
            then 1
            else 0
       end
FROM t;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

If you want check if contain only digit or not try

CASE WHEN  REGEXP_LIKE(Column_name, '^[[:digit:]]+$') THEN 1 ELSE 0 END ;

Upvotes: 3

Related Questions