Re: REGEXP Conversion

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 10 Feb 2010 19:05:10 +0100
Message-ID: <4B72F556.4070605_at_gmail.com>

On 10.02.2010 18:55, Dave wrote:

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

Not quite. Solution provided by Alvaro will pick up as well 815, (if 815 is at the beginning)
,815 (if 815 is at the end)
,815, (if 815 is somethere in the middle)

The case where 815 doesn't have come neither in front of nor behind - it won't be covered by this solution ( equally by mine equally by your). If you have different cases which all have to be covered by regexp (or any other expression) - *you* have to prepare *representative* test data (for example with CTE) and explain the rules, not making other people guessing on them.

Best regards

Maxim Received on Wed Feb 10 2010 - 12:05:10 CST