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?
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.