CODEWITHSUNDEEP
sqlregexoracle-database
Nick
Nick

Reputation: 3845

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

Upvotes: 1

Views: 1162

Answers (1)

trincot
trincot

Reputation: 350034

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.

Upvotes: 4

Related Questions