Re: REGEXP Conversion

Giganews Newsgroups
Subject: Re: REGEXP Conversion
Posted by:  Álvaro_G._Vicario
Date: Wed, 10 Feb 2010

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

Replies

In response to

REGEXP Conversion posted by Dave on Wed, 10 Feb 2010