Forum Stats

  • 3,838,263 Users
  • 2,262,349 Discussions
  • 7,900,568 Comments

Discussions

Search exact word in a sentence without using CONTAINS operator

OraDev16
OraDev16 Member Posts: 105 Red Ribbon
edited Jul 20, 2022 3:13PM in SQL & PL/SQL

Hi All,

I've the following table with five records and I want to search the exact term 'auto' expecting two records to be returned (2, 5 and 6). Is it possible with like operator, instr function or regular expression. I can't use CONTAINS operator (Oracle Text Search) due to some development restrictions.

create table test(

id number,

name varchar2(100)); 


insert into test values(1,'automation increase efficiency');

insert into test values(2,'Auto');

insert into test values(3,'this record does not contain the search term');

insert into test values(4,'this is an automatic reply');

insert into test values(5,'Auto Renewal');

insert into test values(6,'Turn off auto tune');


Thank you.

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,229 Red Diamond
    edited Jul 20, 2022 3:19PM Answer ✓

    Hi, @OraDev16

    Thanks for posting the sample data; that really helps!

    Depending on exactly what you consider to be a "word":

    SELECT   *
    FROM	 test
    WHERE	 REGEXP_LIKE ( name
    	 	     , '(^|[^[:alpha:]])auto($|[^[:alpha:]])'
    		     , 'i'	-- case insensitive
    		     )
    ORDER BY id
    ;
    

    This looks for 'auto' preceded and followed by a non-letter (if anything), so "123auto4' and 'auto,' would be included. If you want to exclude '123auto4', then use [:alnum:] instead of [:alpha:] .

    OraDev16

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,229 Red Diamond
    edited Jul 20, 2022 3:19PM Answer ✓

    Hi, @OraDev16

    Thanks for posting the sample data; that really helps!

    Depending on exactly what you consider to be a "word":

    SELECT   *
    FROM	 test
    WHERE	 REGEXP_LIKE ( name
    	 	     , '(^|[^[:alpha:]])auto($|[^[:alpha:]])'
    		     , 'i'	-- case insensitive
    		     )
    ORDER BY id
    ;
    

    This looks for 'auto' preceded and followed by a non-letter (if anything), so "123auto4' and 'auto,' would be included. If you want to exclude '123auto4', then use [:alnum:] instead of [:alpha:] .

    OraDev16
  • OraDev16
    OraDev16 Member Posts: 105 Red Ribbon

    Thank you Frank; It's working like a charm.

  • OraDev16
    OraDev16 Member Posts: 105 Red Ribbon
    edited Aug 2, 2022 4:11PM

    Hi Frank,

    Everything remains the same except adding the following record:

    insert into test9 values(7,'Turn off *auto* tune');

    And, when I use the query:

    SELECT  *

    FROM test9

    WHERE REGEXP_LIKE ( name

      , '(^|[^[:alpha:]])Turn off auto tune($|[^[:alpha:]])'

      , 'i' -- case insensitive

      )

    ORDER BY id;

    It returns the record bearing the id # 6. I want have both id # 6 and 7. Question is how i should include special characters (!,@,#,$,%,(), * etc. ) in the query.

    Note:

    Tried putting the special characters as follows:

    '(^|[^[:alpha:]*])Turn off auto tune($|[^[:alpha:]*])'

    '(^|[^[:alpha:]]*)Turn off auto tune($|[^[:alpha:]]*)'

    But it didn't work

    Thank you.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,229 Red Diamond

    Hi, @OraDev16

    Earlier, you saw how to find some given text sandwiched in between delimiters. The stuff inside the parentheses was the "bread" of the sandwich, and the text you wanted to find (e.g. 'auto') was the "filling".

    Tried putting the special characters as follows:

    '(^|[^[:alpha:]*])Turn off auto tune($|[^[:alpha:]*])'

    You tried to change the bread. What you want to do in this case is change the filling, like this:

    SELECT   *
    FROM	 test
    WHERE	 REGEXP_LIKE ( name
    	 	     , '(^|[^[:alpha:]])Turn off \*?auto\*? tune($|[^[:alpha:]])'
    		     , 'i'	-- case insensitive
    		     )
    ORDER BY id
    ;
    

    Here, '?' means the preceding sub-string is optional (that is, it may occur 0 or 1 times). Since '*' has a special meaning in regular expressions, we need to escape it (that is, use '\*' instead of '*') to treat it literally.