Nick Nick - 1 month ago 6
SQL Question

Oracle SQL Regular expression to match Varchar column - treat as numbers

I have a table with a column whose data type is VARCHAR2(7 Char). The data in this column has values such as 1006,1007,2002 etc

I would like a regular expression that matches 4002,4003,4005,4011,4013 but NOT 4001.

First i tried using

To_number(columnName) > 4001
but got an ORA-01722 invalid number error.

I have then tried using
Regexp_like
unsuccessfully for this. I tried:

1. 40[02,03,05,11,13]
2. 40[0,1][^01]
3. 40[0,1]([2,3,5,11])


Any help would be appreciated!

cheers

Answer

You could just use this condition:

 col in ('4002','4003','4005','4011','4013')

If you really need to have to do it with regexp_like, then there are several ways to do it. Here are a few in order of decreasing readability and length:

 regexp_like(col, '^(4002|4003|4005|4011|4013)$')
 regexp_like(col, '^40(02|03|05|11|13)$')
 regexp_like(col, '^40(0[235]|1[13])$')

Do notice that classes in regular expressions ([ ... ]) list individual characters to match, not sequences.

Comments