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

1
votes

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

1
sqlregexoracle
If you got ORA-01722 then at least some of the values in your column are not numbers (or, rather, strings that can be converted to numbers). Why isn't the column number? You should be using the correct data type. You've given some values that should and shouldn't natch but not a general rule. Your attempts suggest it has to start with 40, but what about 4020, or 4099, or any other number you haven't shown? – Alex Poole
Why not just a simple col in ('4002','4003','4005','4011','4013')? – trincot
So the column is declared 7 characters, but the values are ALL four characters? Or are you looking for 4002 anywhere in a string that might be seven characters? – mathguy
Hi, thanks for the replies. I only have read access to the database, so i cant modify table etc. You are right some of the data in that column contains a letter at the end and i guess thats why i get the oracle error. @trincot - im trying to eliminate the values i listed in a where clause which has other AND clauses so that wouldnt do in this situation. cheers – Nick
@mathguy - these entries that im looking to capture with regex are all 4 characters long and not contained in other strings – Nick

1 Answers

5
votes

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.