0

I've tried [^A-Za-z0-9]tent[^A-Za-z0-9] .. with multiple combinations of ?+*

as well as something like [^|[^A-Za-z0-9]]tent[[^A-Za-z0-9]|$]

I want to match:

  • attention - NO
  • look at the tent - YES
  • tent's are nice - YES
  • something tent something - YES
  • camping,tent,outdoors - YES (some non-alphanumeric delimiter)

if it makes it easier something like "tenting" can also be a match

I suppose I'm missing something from all the pages I've looked at, but I'm really stuck here. I need to find a search word, at the start, middle or end of a sentence or delimited list. Do I need to do multiple statements?

I was hoping there might be a way to say use this expression but not if it's the start of the line or the end of the line

Emma
  • 167
  • 1
  • 9
  • 1
    I have no idea about Oracle patterns, but can you use `\b` to denote a boundry? i.e. `\btent\b` or `\btent(ing)?\b` – freefaller Feb 05 '13 at 17:09
  • 1
    try .*\btent\b.* ^ in [] is not – paparazzo Feb 05 '13 at 17:10
  • usually `[^..]` the `^` indicates NOT a member of the enclosed character class. In Unix regexs, you should just be able to say `tent` and it would match any of your examples. Worst case would be `.*tent.*`. I'm not sure if oracle regex's follow the same "rules". Good luck. – shellter Feb 05 '13 at 17:11
  • It doesn't look like it as it's not returning anything (the \b) – Emma Feb 05 '13 at 17:12
  • my problem is .*tent.* would match "attention" which I don't want it to do. otherwise I'd just do %tent% and be done with it. – Emma Feb 05 '13 at 17:13
  • 1
    Possible solution [found here](http://stackoverflow.com/questions/7567700/oracle-regexp-like-and-word-boundaries)... try `(^|\s)tent(\s|$)` – freefaller Feb 05 '13 at 17:16
  • possibly this? `where REGEXP_LIKE(your_str, '(\W|^)tent(\W|$)', 'i')` would match the ones you want. – DazzaL Feb 05 '13 at 17:20

1 Answers1

3

with \w (non alpha numeric)

SQL> select str
  2    from (select 'attention' str from dual union all
  3          select 'look at the tent' str from dual union all
  4          select 'tent''s are nice' str from dual union all
  5          select 'something tent something' str from dual union all
  6          select 'camping,tent,outdoors' str from dual)
  7   where REGEXP_LIKE(str, '(\W|^)tent(\W|$)', 'i') ;

STR
------------------------
look at the tent
tent's are nice
something tent something
camping,tent,outdoors

SQL> select str,
  2         case when REGEXP_LIKE(str, '(\W|^)tent(\W|$)', 'i') then 'YES' else 'NO' end matches
  3    from (select 'attention' str from dual union all
  4          select 'look at the tent' str from dual union all
  5          select 'tent''s are nice' str from dual union all
  6          select 'something tent something' str from dual union all
  7          select 'tent' str from dual union all
  8          select 'camping,tent,outdoors' str from dual);

STR                      MAT
------------------------ ---
attention                NO
look at the tent         YES
tent's are nice          YES
something tent something YES
tent                     YES
camping,tent,outdoors    YES
DazzaL
  • 21,638
  • 3
  • 49
  • 57
  • The \W works, so did what I had if I put it in () instead of [] for the outer set. Thanks for your help. – Emma Feb 05 '13 at 17:25