Re: REGEXP Conversion

From: Dave <david.c.watson_at_gmail.com>
Date: Wed, 10 Feb 2010 09:55:47 -0800 (PST)
Message-ID: <a90a04da-a07b-4c95-8908-5243b500414f_at_z17g2000yqh.googlegroups.com>

On Feb 10, 11:48 am, "Álvaro G. Vicario" <alvaro.NOSPAMTH..._at_demogracia.com.invalid> wrote:
> El 10/02/2010 18:21, Dave escribió/wrote:
>
>
>
> > 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.
> > 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
>
> If you are trying to match rows with the 918 "subvalue", I believe the
> standard approach is something like:
>
> SELECT *
> FROM demo_table
> WHERE ',' || code || ',' LIKE '%,918,%'
>
> (Not tested.)
>
> P.S. Why do so many developers think that storing denormalized data in a
> relational database is a good idea?
>
> --
> --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain
> -- Mi sitio sobre programación web:http://borrame.com
> -- Mi web de humor satinado:http://www.demogracia.com
> --

I agree, the design is total bollocks, but it is a vendor application and unfortunately, we cannot change how it works or is designed.

That solution would essentially only give one of the possible scenarios, where the commas are before and after the code.

We need to account for the scenario where that code could show up many different ways, i.e. 815, or ,815 or ,815, or 815. Received on Wed Feb 10 2010 - 11:55:47 CST