Oracle REGEXP_LIKE match non-numeric character

user3923442 picture user3923442 · Mar 26, 2015 · Viewed 7.7k times · Source

I have a set of rows in my database with VARCHAR2 column values like so: tac903.2, tac903.24, tac903.2a, tac903.2b

I want to create a regex that does not match if the last character in the search expression is numeric. So when I search for "tac903.2" I want to only match: tac903.2, tac903.2a, and tac903.2b. This should be simple but I'm not getting the desired behaviour from my regex here:

REGEXP_LIKE(col, 'tac903.2[a-z]?'); //This is matching the tac903.24 record.

REGEXP_LIKE(col, 'tac903.2[^0-9]?'); //This also is matching the tac903.24 record.

I'm a beginner with regular expressions, but based on my research it seems like the above should achieve the behaviour I'm looking for. Can someone tell me what's wrong with them?

Answer

Mark Leiber picture Mark Leiber · Mar 26, 2015

Set up sample data:

create table temp_matches (
  rec varchar2(10)
);

insert into temp_matches values ('tac903.2');
insert into temp_matches values ('tac903.2a');
insert into temp_matches values ('tac903.2b');
insert into temp_matches values ('tac903.24');
insert into temp_matches values ('2');
insert into temp_matches values ('a');

Query:

select *
from temp_matches
where REGEXP_LIKE(rec, '(.)*[^0-9]$');

Results:

tac903.2a
tac903.2b
a

The $ indicates the end of the line, so we just look for any values not ending in 0-9.