Re: REGEXP Conversion

From: Tim X <timx_at_nospam.dev.null>
Date: Thu, 11 Feb 2010 20:57:16 +1100
Message-ID: <87eiks9jsz.fsf_at_lion.rapttech.com.au>

Dave <david.c.watson_at_gmail.com> writes:

> 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.

don't overlook the concatenation on the LHS. Because of the ','|| code ||',' ALL values will have at least 1 leading and trailing comma regardless of where they are in the list. In some cases, it will have two ie ,,815,, but that still matches the like clause.

Tim

-- 
tcross (at) rapttech dot com dot au

Received on Thu Feb 11 2010 - 03:57:16 CST