Re: REGEXP Conversion
From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com.invalid>
Date: Wed, 10 Feb 2010 18:48:12 +0100
Message-ID: <hkurgs$ej5$1_at_news.eternal-september.org>
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.
Date: Wed, 10 Feb 2010 18:48:12 +0100
Message-ID: <hkurgs$ej5$1_at_news.eternal-september.org>
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 --Received on Wed Feb 10 2010 - 11:48:12 CST