6 Replies Latest reply: Jan 8, 2013 7:03 PM by Solomon Yakobson RSS

    Filter LIKE ANY

    ysri
      Hi, I want to rewrite this sql filter condition:
      where col1 like '%a' or col1 like 'b%' or col1 like '%c%' or col1 like 'd'

      into more simpler format like the way Teradata allows:
      where col1 like any ('%a','b%','%c%','d')

      What's the equivalent syntax in Oracle? Can REGEXP_LIKE be used?
      Any working example please?

      Similarly, simpler format for:
      where col1 not like '%a' and col1 not like 'b%' and col1 not like '%c%' and col1 not like 'd'


      Thanks,
      -srinivas y.
        • 1. Re: Filter LIKE ANY
          Peter vd Zwan
          Hi,

          Try this:
          with data_list as
          (
          select 'abc' col from dual union all
          select 'd' col from dual union all
          select 'bc' col from dual union all
          select 'abcde' col from dual union all
          select 'abclskdajhasdlfkj' col from dual union all
          select 'abcdsafasdfa' col from dual 
          )
          ,like_list as
          (
          select '%a' like_col from dual union all
          select 'b%' like_col from dual union all
          select '%c' like_col from dual union all
          select 'd' like_col from dual 
          )
          select distinct
            d.*
          
          from
            data_list     d     join
            like_list     l     on (d.col like l.like_col)
          ;
          COL             
          -----------------
          d                 
          abcdsafasdfa      
          bc                
          abc               
          Regards,

          Peter
          • 2. Re: Filter LIKE ANY
            chris227
            Depending on your requirements you might consinder the use of oracle text. (Be aware of his pitfalls: stopwords, skipjoins etc.)
            drop table e
            create table e as (select * from emp)
            create index idx_e_ename on e(ename)
            INDEXTYPE IS CTXSYS.CONTEXT
            
            select ename from e
            where
            contains( ename, 'a% OR b% OR %c% OR d') > 1
            
            ENAME 
            blake 
            clark 
            scott 
            ALLEN 
            adams 
            
            case sensitive
            
            drop index idx_e_ename
            
            BEGIN
            CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
            CTX_DDL.SET_ATTRIBUTE ('test_lex', 'mixed_case', 'YES');
            END;
            
            create index idx_e_ename on e(ename)
            INDEXTYPE IS CTXSYS.CONTEXT
            PARAMETERS
            ('LEXER test_lex')
            
            select ename from e
            where
            contains( ename, 'a% OR b% OR %c% OR d') > 1
            
            ENAME 
            blake 
            clark 
            scott 
            adams 
            • 3. Re: Filter LIKE ANY
              Solomon Yakobson
              ysri wrote:
              Can REGEXP_LIKE be used?
              regexp_like(col1,'(a$)|(^b)|(c)|(^d$)')
              SY.
              • 4. Re: Filter LIKE ANY
                ysri
                Thanks to all.

                Solomon, your code worked !
                Now, what's the equivalent for NOT like any of those?

                Also, if I want to match strings like *'A.B.C.%.D'*
                where *'.'* is real period in the source string, how do I do the regular expression?

                I see that *'%'* can be replaced by '.*' in the regular expression, but I want the original period in the source string
                to match literally as period itself

                Thanks,
                -sri
                • 5. Re: Filter LIKE ANY
                  Solomon Yakobson
                  ysri wrote:
                  Now, what's the equivalent for NOT like any of those?
                  NOT regexp_like(col1,'(a$)|(^b)|(c)|(^d$)')
                  Also, if I want to match strings like *'A.B.C.%.D'*
                  where *'.'* is real period in the source string, how do I do the regular expression?
                  You escape it with backslash:
                  regexp_like(col1,'^A\.B\.C\..*\.D$')
                  Now back to your original case. Keep in mind, in:
                  where col1 like '%a' or col1 like 'b%' or col1 like '%c%' or col1 like 'd'
                  col1 like 'b%' and col1 like 'd' will use index (assuming col1 is indexed) while:
                  regexp_like(col1,'(a$)|(^b)|(c)|(^d$)')
                  will not.

                  SY.
                  • 6. Re: Filter LIKE ANY
                    ysri
                    Thanks Solomon,
                    in my case all strings will have *%* before and after each search string, so I guess that means index will not be used anyway with traditional sql statement
                    So I guess using regexp_like will not degrade anymore for my scenario !

                    Thanks again, your example and info was very helpful

                    -sri