Oracle REGEXP_LIKE match non-numeric character

Question

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?

Mark Leiber · Accepted Answer · 2015-03-26 15:23:47Z

4

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.

answered Mar 26, 2015 at 15:23

Mark Leiber

3,1382 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3923442 Over a year ago

Thank you! I see that I had to explicitly specify the end of the line in my regex to get the behaviour that I wanted.

vks · Accepted Answer · 2015-03-27 04:18:34Z

0

You can use word boundary [[:>:]] here.

Something like this REGEXP_LIKE(col, 'tac903.2[a-z]?[[:>:]]');

See demo.

http://www.sqlfiddle.com/#!9/940fd/2/0

EDIT:

`REGEXP_LIKE(col, 'tac903.2[a-z]?(\s|$|\W)');`

edited Mar 27, 2015 at 4:18

answered Mar 26, 2015 at 15:15

vks

68.1k11 gold badges96 silver badges132 bronze badges

2 Comments

user3923442 Over a year ago

I can see this works in the demo you posted, but in oracle ":>:" isn't recognized as a character class. Thank you for your answer though!

David Faber Over a year ago

Oracle regex does not support word boundaries, whether the regular sort (\b) or POSIX-style ([[:<:]] and [[:>:]]).

Collectives™ on Stack Overflow

Oracle REGEXP_LIKE match non-numeric character

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related