1

I'm looking to find all occurrences of "Dr. Jones" in a name field using regexp_like. I can do the OR (all names with DR or JONES in it) but I need both names to be present in the field.

Is there a simple way to do this with regexp_like without using multiple statements?

I'm looking for a shortcut way. The answer below gives it in an easy situation. I know I can have:

where regexp_like(color_code,'red|blue|green','i');

and it will give me any record of color_code with either of those three names in it. What I'm looking for is something like:

where regexp_like(color_code,'red&blue&green','i');

that would only select records with ALL THREE colors in the name but they can be in any order so: red blue green, green purple blue black red, yellow red orange green blue

would all make the list

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
user4893573
  • 13
  • 4

1 Answers1

0
select * from mytable
where regexp_like(txt,'(Dr.+Jones+)|(Jones.+Dr+)','i');

ok took away the need for period after dr and allowed the words to be switched

Bryan Dellinger
  • 4,724
  • 7
  • 33
  • 79
  • here is the fiddle for it I added your two examples http://sqlfiddle.com/#!4/b78944/2 – Bryan Dellinger May 13 '15 at 00:50
  • no THIS works the way I want it thanks for this!!!! been racking my brain all day on how to figure this out – user4893573 May 13 '15 at 01:03
  • @user2744722 - you should have *edited your original answer* with a corrected solution instead of polluting the thread with a second response. Please delete your original answer. (You could also tidy up this one so it makes sense without the first response). – APC May 13 '15 at 05:34
  • 1
    More importantly your `regex` includes false positives for any sentence with DR and JONES. Check out my [version of your SQLFiddle](http://sqlfiddle.com/#!4/a32cd/1). It always pays to include test data we expect not to appear in the result set. – APC May 13 '15 at 05:41