Simple regular expression in Oracle

Question

I have a table with the following values:

ID     NAME        ADDRESS
1      Bob         Super stree1 here goes
2      Alice       stree100 here goes
3      Clark       Fast left stree1005
4      Magie       Right stree1580 here goes

I need to make a query using LIKE and get only the row having stree1 (in this case only get the one with ID=1) and I use the following query:

select * from table t1 WHERE t1.ADDRESS LIKE '%stree1%';

But the problem is that I get all rows as each of them contains stree1 plus some char/number after.

I have found out that I can use REGEXP_LIKE as I am using oracle, what would be the proper regex to use in:

select * from table t1 WHERE regexp_like(t1.ADDRESS ,'stree1');

Michael Goldshteyn · Accepted Answer · 2010-10-16 16:48:11Z

4

I would think that this would be the reg-ex you are seeking:

select * from table t1 WHERE regexp_like(t1.ADDRESS ,'stree1(?:[^[:word:]]|$)');

If you want to, you can further simplify this to:

select * from table t1 WHERE regexp_like(t1.ADDRESS ,'stree1(?:\W|$)');

That is, 'stree1' is not followed by a word character (i.e., is followed by space/punctuation/etc...) or 'stree1' appears at the end of the string. Of course there are many other ways to do the same thing, including word boundaries 'stree1\b', expecting particular characters after the 1 in stree1 (e.g., a white-space with 'stree1\s'), etc...

edited Oct 16, 2010 at 16:48

answered Oct 16, 2010 at 16:29

Michael Goldshteyn

74.9k25 gold badges136 silver badges183 bronze badges

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

2 Comments

tinifni Over a year ago

That may return the character after stree1 (non-word or EOL). You may need to use stree1(?=\W|$) if it does add unwanted characters or use stree1\b which matches any word boundary.

Michael Goldshteyn Over a year ago

In this SQL query, it's not a matter of what the regex would "return", but simply what it would match. If he was actually capturing, then a forward lookahead would be more appropriate.

tinifni · Accepted Answer · 2010-10-16 16:34:35Z

0

This may help:

stree1\b

answered Oct 16, 2010 at 16:34

tinifni

2,40219 silver badges15 bronze badges

Comments

kleopatra · Accepted Answer · 2012-12-25 13:02:39Z

0

The first '\W' is tells it it a non-word character since you need noting after 'stree1' but space and '$' tells take it as a valid string if it ends with stree1

select *
from table1
where regexp_like(address,'stree1(\W|$)')

edited Dec 25, 2012 at 13:02

kleopatra

51.6k28 gold badges103 silver badges220 bronze badges

answered Dec 25, 2012 at 12:46

sirak

114 bronze badges

Collectives™ on Stack Overflow

Simple regular expression in Oracle

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related