Validate one column only has numbers using Regular Expression

Question

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

ScaisEdge · Accepted Answer · 2019-04-25 16:37:47Z

3

If you want check if contain only digit or not try

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

answered Apr 25, 2019 at 16:37

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kaushik Nayak · Accepted Answer · 2019-04-26 03:15:56Z

1

You may use TRANSLATE function

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

answered Apr 26, 2019 at 3:15

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

Validate one column only has numbers using Regular Expression

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related