Re: REGEXP Conversion

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 10 Feb 2010 18:54:06 +0100
Message-ID: <4B72F2BE.5010209_at_gmail.com>

On 10.02.2010 18:21, Dave wrote:
> Hi,
>
> Is anyone good at converting the following sql statement's LIKE
> clauses into 1 coherent REGEXP_LIKE? I came up with a couple of
> solutions but am interested in seeing if anyone has a more efficient
> way then what I produced.
>
> Thanks,
> Dave
>
> SELECT *
> FROM demo_table
> WHERE TRIM (code) = TRIM ('918')
> OR TRIM (REGEXP_REPLACE (code, ' *, *', ',')) LIKE '%,' || TRIM
> ('918')
> OR TRIM (REGEXP_REPLACE (code, ' *, *', ',')) LIKE TRIM ('918') ||
> ',%'
> OR TRIM (REGEXP_REPLACE (code, ' *, *', ',')) LIKE '%,' || TRIM
> ('918') || ',%';
>
> Code Column has data stored with 1 to many codes, seperated by
> commas...
>
> i.e. 829,58,189,918

It is not quite clear, what exact input data do you have. Besides that, your sql at some places doesn't seem to be efficient 1) You don't need to trim function in this expression trim('918') 2) If you want get all you spaces you can simply use replace instead of nested trim(regexp_replace
3) If your data are comma separated values and blanks can occur everywhere in that list and you will in every case use regular expressions, then something like this can do it:

SQL> with t as (

   2      select 1 id, '829,58,189,918' code from dual union all
   3      select 2,'829,58,189,  918' code from dual union all
   4      select 3,' 829, 58,189, 918 ' code from dual union all
   5      select 4,' 918 ,829, 58, 189' code from dual union all
   6      select 5,' 918 ,829, 58, 189' code from dual union all
   7      select 6,'58  ,829, 918 , 189' code from dual union all
   8      select 7,'58  ,829, 918, 189' code from dual union all
   9      select 8,'918 ,829, 58, 189' code from dual union all
  10      select 9,' 918 ' code from dual union all
  11      select 10,' 9180 ,829, 58, 189' code from dual union all
  12      select 11,' 918 ; 829, 58, 189' code from dual union all
  13      select 12,'  0918 ,829, 58, 189' code from dual

  14 )
  15 select t.*,
  16 case

  17      when regexp_like(code,'(^|,)\s*918\s*(,|$)') then 'Match'
  18      else 'No Match'

  19 end regexp_match
  20 from t
  21 ;

         ID CODE                 REGEXP_M
---------- -------------------- --------
          1 829,58,189,918       Match
          2 829,58,189,  918     Match
          3  829, 58,189, 918    Match
          4  918 ,829, 58, 189   Match
          5  918 ,829, 58, 189   Match
          6 58  ,829, 918 , 189  Match
          7 58  ,829, 918, 189   Match
          8 918 ,829, 58, 189    Match
          9  918                 Match
         10  9180 ,829, 58, 189  No Match
         11  918 ; 829, 58, 189  No Match
         12   0918 ,829, 58, 189 No Match

12 rows selected.

(to produce output like yours, simply take regexp_replace from case into where clause)

You can do it as well without regexp (should be more effective), instead of like can be used instr as well :

SQL> with t as (

   2      select 1 id, '829,58,189,918' code from dual union all
   3      select 2,'829,58,189,  918' code from dual union all
   4      select 3,' 829, 58,189, 918 ' code from dual union all
   5      select 4,' 918 ,829, 58, 189' code from dual union all
   6      select 5,' 918 ,829, 58, 189' code from dual union all
   7      select 6,'58  ,829, 918 , 189' code from dual union all
   8      select 7,'58  ,829, 918, 189' code from dual union all
   9      select 8,'918 ,829, 58, 189' code from dual union all
  10      select 9,' 918 ' code from dual union all
  11      select 10,' 9180 ,829, 58, 189' code from dual union all
  12      select 11,' 918 ; 829, 58, 189' code from dual union all
  13      select 12,'  0918 ,829, 58, 189' code from dual

  14 )
  15 select t.*,
  16 case

  17      when ','||replace(code,' ')||',' like '%,918,%' then 'Match'
  18      else 'No Match'

  19 end regexp_match
  20 from t
  21 ;

         ID CODE                 REGEXP_M
---------- -------------------- --------
          1 829,58,189,918       Match
          2 829,58,189,  918     Match
          3  829, 58,189, 918    Match
          4  918 ,829, 58, 189   Match
          5  918 ,829, 58, 189   Match
          6 58  ,829, 918 , 189  Match
          7 58  ,829, 918, 189   Match
          8 918 ,829, 58, 189    Match
          9  918                 Match
         10  9180 ,829, 58, 189  No Match
         11  918 ; 829, 58, 189  No Match
         12   0918 ,829, 58, 189 No Match

12 rows selected.

Best regards

Maxim Received on Wed Feb 10 2010 - 11:54:06 CST