SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to check value in a comma delimiter in sql query

VimalPSNov 1 2018 — edited Nov 2 2018

Hi Folks,

I have a column in table which has data like this.

So, my requirement is just to return rows which having string only as 'toys' in this comma separated value.

ie.in my output should be something like this..

toys

toys,toys

toys,toys,toys

Is it possible to achieve it in a where condition directly.Any regex is available to check this condition.

select item

from table

where (...condition...) = 'toys';

Please help on this problem.

Thank You.

This post has been answered by VimalPS on Nov 1 2018

Jump to Answer

Arun Kumar Gupta

This should work for you

WITH T AS

(

SELECT 'toys' item from DUAL UNION ALL

SELECT 'toys,board' item from DUAL UNION ALL

SELECT 'toys' item from DUAL UNION ALL

SELECT 'toys,toys' item from DUAL UNION ALL

SELECT 'toys,soap' item from DUAL UNION ALL

SELECT 'toys,toys,toys' item from DUAL UNION ALL

SELECT 'toys,toys,book,toys' item from DUAL

)

SELECT item, REPLACE(REPLACE (item,'toys'),',') non_toys

FROM t

WHERE REPLACE(REPLACE (item,'toys'),',') IS NULL ;

Regards

Arun

VimalPS

Answer

Hi Arun,

Thanks for the quick reply. There is one condition i missed. A null condition.

WITH T AS

(

SELECT 'toys' item from DUAL UNION ALL

SELECT null item from DUAL UNION ALL -- this one

SELECT 'toys,board' item from DUAL UNION ALL

SELECT 'toys' item from DUAL UNION ALL

SELECT 'toys,toys' item from DUAL UNION ALL

SELECT 'toys,soap' item from DUAL UNION ALL

SELECT 'toys,toys,toys' item from DUAL UNION ALL

SELECT 'toys,toys,book,toys' item from DUAL

)

SELECT item, REPLACE(REPLACE (item,'toys'),',') non_toys

FROM t

WHERE REPLACE(REPLACE (nvl(item,'aa'),'toys'),',') IS NULL ;

So i handled with a nvl condtion. This will work perfect.right?. Or any other good way to handle null condition?.

Thank You.

Marked as Answer by VimalPS · Sep 27 2020

Arun Kumar Gupta

Thats Fine.

You can also use query like this

SELECT item, REPLACE(REPLACE (item,'toys'),',') non_toys

FROM t

WHERE REPLACE(REPLACE (item,'toys'),',') IS NULL

AND item is not null;

Regards

Arun

Pablolee

Using regexp:

 WITH T AS  
 (SELECT 'toys' item from DUAL UNION ALL  
 SELECT 'toys,board' item from DUAL UNION ALL  
 SELECT 'toys' item from DUAL UNION ALL  
 SELECT 'toys,toys' item from DUAL UNION ALL  
SELECT 'toys,soap' item from DUAL UNION ALL  
SELECT 'toys,toys,toys' item from DUAL UNION ALL  
SELECT 'toys,toys,book,toys' item from DUAL  
    )  
SELECT item
from t
where not REGEXP_LIKE(item||',', '[^toys,]')

1 person found this helpful

Ashu_Neo

You can try like below too. Let me know for any concerns further.


 
WITH T
     AS (SELECT 'toys' item FROM DUAL
         UNION ALL
         SELECT 'toys,board' item FROM DUAL
         UNION ALL
         SELECT 'toys' item FROM DUAL
         UNION ALL
         SELECT 'toys,toys' item FROM DUAL
         UNION ALL
         SELECT 'toys,soap' item FROM DUAL
         UNION ALL
         SELECT 'toys,toys,toys' item FROM DUAL
         UNION ALL
         SELECT 'toys,toys,book,toys' item FROM DUAL)
SELECT *
  FROM t
 WHERE REGEXP_COUNT (item, 'toys') - REGEXP_COUNT (item, ',') = 1;

1 person found this helpful

Etbin

Another way (using no regexp)

with

data as

(

SELECT 'toys' item from DUAL UNION ALL

SELECT 'toys,board' item from DUAL UNION ALL

SELECT 'toys' item from DUAL UNION ALL

SELECT 'toys,toys' item from DUAL UNION ALL

SELECT 'toys,soap' item from DUAL UNION ALL

SELECT 'toys,toys,toys' item from DUAL UNION ALL

SELECT 'toys,toys,book,toys' item from DUAL

)

select item

from data

where instr(','||item||',',','||'toys'||',') > 0

and ltrim(item,'toys,') is null

ITEM
toys
toys
toys,toys
toys,toys,toys

Regards

Etbin

mNem

> WITH T AS  
(  
SELECT 'toys' item from DUAL UNION ALL  
SELECT ',toys' item from DUAL UNION ALL 
SELECT ',TOYs' item from DUAL UNION ALL 
SELECT null item from DUAL UNION ALL                     -- this one 
SELECT ',' item from DUAL UNION ALL  
SELECT 'toys,board' item from DUAL UNION ALL  
SELECT 'toys' item from DUAL UNION ALL  
SELECT 'toys,toys' item from DUAL UNION ALL  
SELECT 'toys,soap' item from DUAL UNION ALL  
SELECT 'toys,toys,toys' item from DUAL UNION ALL  
SELECT 'toys,toys,book,toys' item from DUAL  
)  
SELECT item, case when regexp_like(item, '^([,]?toys[,]?)+$', 'i') then 'T' else 'F' end non_toys  
FROM t  
--WHERE  regexp_like(item, '^([,]?toys[,]?)+$', 'i')
ITEM                NON_TOYS
------------------- --------
toys                T        
,toys               T        
,TOYs               T        
                    F        
,                   F        
toys,board          F        
toys                T        
toys,toys           T        
toys,soap           F        
toys,toys,toys      T        
toys,toys,book,toys F        
 11 rows selected

BluShadow

The one thing nobody's mentioned yet is that you should fix your data model.

Currently you are breaching 1st Normal Form of relational database design, by storing more than one value in a single column.

That should be normalised so that you have two tables, one for the base data and one for the multiple values, each stored in separate rows.

Then you wouldn't have any problem querying the data you want.

1 person found this helpful

Paulzip

...

where regexp_like (item, '(^|,)toys(,|$)', 'i')

However, my advice is normalise your data. You are breaking first normal form rules (column values should be atomic)

VimalPS

Thanks for the reply.

In one way it's returning correct result set. And that's correct too.

If my data set has something like this 'toys-i,toys' or '( toys CAT )' it'll return that rows too, which is not likely the result set which i needed.But yes, i didn't mention that in my question earlier.

WITH T

 AS (SELECT 'toys' item FROM DUAL

     UNION ALL

     SELECT 'toys,board' item FROM DUAL

     UNION ALL

     SELECT 'toys' item FROM DUAL

     UNION ALL

     SELECT 'toys,toys' item FROM DUAL

     UNION ALL

     SELECT 'toys-i,toys' item FROM DUAL

     UNION ALL

     SELECT '(   toys   CAT  )' item FROM DUAL

     UNION ALL

     SELECT 'toys,soap' item FROM DUAL

     UNION ALL

     SELECT 'toys,toys,toys' item FROM DUAL

     UNION ALL

     SELECT 'toys,toys,book,toys' item FROM DUAL)

SELECT *

FROM t

WHERE REGEXP_COUNT (item, 'toys') - REGEXP_COUNT (item, ',') = 1;

Result:

Cookiemonster76

The solutions in replies 3, 6 and 7 will work for your revised data set (and also the null, which you left out - stop doing that).

VimalPS

Yes.That's correct.But unfortunately i cannot decide on that because it's all up to the senior level peoples who take care of design.We had suggested this option long back.

Solomon Yakobson

No need for regular expressions if we are looking strictly for comma-separated string of word toys:

WITH T

AS (SELECT 'toys' item FROM DUAL

UNION ALL

SELECT 'toys,board' item FROM DUAL

UNION ALL

SELECT 'toys' item FROM DUAL

UNION ALL

SELECT 'toys,toys' item FROM DUAL

UNION ALL

SELECT 'toys-i,toys' item FROM DUAL

UNION ALL

SELECT '( toys CAT )' item FROM DUAL

UNION ALL

SELECT 'toys,soap' item FROM DUAL

UNION ALL

SELECT 'toys,toys,toys' item FROM DUAL

UNION ALL

SELECT 'toys,toys,book,toys' item FROM DUAL

UNION ALL

SELECT 'toys,toys,toyshop,toys' item FROM DUAL

UNION ALL

SELECT 'toys,toys,tinkertoys,toys' item FROM DUAL

UNION ALL

SELECT 'toys,toys,tinkertoyshop,toys' item FROM DUAL

)

SELECT *

FROM T

WHERE REPLACE(item || ',','toys,') IS NULL

ITEM

----------------------------

toys

toys,toys

toys,toys,toys

SQL>

SY.

Paulzip

Solomon Yakobson wrote:

No need for regular expressions if we are looking strictly for comma-separated string of word toys:

SY.

I think a replace approach is inefficient - for example if the text is thousands of chars long, and has a large number of 'toys' occurrences. Regexp_like can short circuit evaluate on the first occurrence.

mathguy

I thought REPLACE also works in a single pass over the string. Does it not?

Solomon Yakobson

First you need to write regexp_like that selects rows containing nothing but comma-separated set of word 'toys'

where regexp_like (item, '(^|,)toys(,|$)', 'i')

doesn't do that.

SY.

Solomon Yakobson

SQL> with t as (

2 select 'junk' || lpad(',toys',3995,',toys') item

3 from dual

4 connect by level <= 10000

5 )

6 select *

7 from t

8 where regexp_like (item,'^toys(,toys)*$')

9 /

no rows selected

Elapsed: 00:00:04.27

SQL> with t as (

2 select 'junk' || lpad(',toys',3995,',toys') item

3 from dual

4 connect by level <= 10000

5 )

6 select *

7 from t

8 WHERE REPLACE(item || ',','toys,') IS NULL

9 /

no rows selected

Elapsed: 00:00:00.09

SQL>

As you can see, item starts with word junk, so, if regexp_like would use short circuit it would exit right away.

SY.

mathguy

Ashu_Neo wrote:
You can try like below too. Let me know for any concerns further.
WITH T
 AS (SELECT 'toys' item FROM DUAL
 UNION ALL
 SELECT 'toys,board' item FROM DUAL
 UNION ALL
 SELECT 'toys' item FROM DUAL
 UNION ALL
 SELECT 'toys,toys' item FROM DUAL
 UNION ALL
 SELECT 'toys,soap' item FROM DUAL
 UNION ALL
 SELECT 'toys,toys,toys' item FROM DUAL
 UNION ALL
 SELECT 'toys,toys,book,toys' item FROM DUAL)
SELECT *
 FROM t
 WHERE REGEXP_COUNT (item, 'toys') - REGEXP_COUNT (item, ',') = 1;

This will not work. For example:

WITH T AS (SELECT 'toys,tinkertoys,toys' item FROM DUAL)
SELECT *
FROM t
WHERE REGEXP_COUNT (item, 'toys') - REGEXP_COUNT (item, ',') = 1;

ITEM
--------------------
toys,tinkertoys,toys

1 person found this helpful

mathguy

I see - I wasn't thinking straight. I thought Paul meant that REPLACE somehow would take longer than a regexp approach that did the same thing. He wasn't; he even explained it, I just didn't get it - by the time I got to that part of his short reply, I was already misinterpreting his meaning in my mind. Thank you for clarifying.

mathguy

Arun Kumar Gupta wrote:

This should work for you

WITHTAS
(
SELECT'toys'itemfromDUALUNIONALL
SELECT'toys,board'itemfromDUALUNIONALL
SELECT'toys'itemfromDUALUNIONALL
SELECT'toys,toys'itemfromDUALUNIONALL
SELECT'toys,soap'itemfromDUALUNIONALL
SELECT'toys,toys,toys'itemfromDUALUNIONALL
SELECT'toys,toys,book,toys'itemfromDUAL
)
SELECTitem,REPLACE(REPLACE(item,'toys'),',')non_toys
FROMt
WHEREREPLACE(REPLACE(item,'toys'),',')ISNULL;

WITH T AS ( SELECT 'toys' item from DUAL UNION ALL SELECT 'toys,board' item from DUAL UNION ALL SELECT 'toys' item from DUAL UNION ALL SELECT 'toys,toys' item from DUAL UNION ALL SELECT 'toys,soap' item from DUAL UNION ALL SELECT 'toys,toys,toys' item from DUAL UNION ALL SELECT 'toys,toys,book,toys' item from DUAL ) SELECT item, REPLACE(REPLACE (item,'toys'),',') non_toys FROM t WHERE REPLACE(REPLACE (item,'toys'),',') IS NULL ;

Regards

Arun

I don't think this is right either. Consider:

WITH T AS
(
SELECT 'toys,,,toys' item from DUAL UNION ALL
SELECT 'toys,soap' item from DUAL UNION ALL
SELECT 'toys,toystoystoys,toys' item from DUAL UNION ALL
SELECT 'toys,toys,book,toys' item from DUAL
)
SELECT item, REPLACE(REPLACE (item,'toys'),',') non_toys
FROM t
WHERE REPLACE(REPLACE (item,'toys'),',') IS NULL ;

ITEM NON_TOYS
---------------------- ----------------------
toys,,,toys
toys,toystoystoys,toys

mathguy

Pablolee wrote:
Using regexp:

WITHTAS
(SELECT'toys'itemfromDUALUNIONALL
SELECT'toys,board'itemfromDUALUNIONALL
SELECT'toys'itemfromDUALUNIONALL
SELECT'toys,toys'itemfromDUALUNIONALL
SELECT'toys,soap'itemfromDUALUNIONALL
SELECT'toys,toys,toys'itemfromDUALUNIONALL
SELECT'toys,toys,book,toys'itemfromDUAL
)
SELECTitem
fromt
wherenotREGEXP_LIKE(item||',','[^toys,]')

 WITH T AS   (SELECT 'toys' item from DUAL UNION ALL   SELECT 'toys,board' item from DUAL UNION ALL   SELECT 'toys' item from DUAL UNION ALL   SELECT 'toys,toys' item from DUAL UNION ALL  SELECT 'toys,soap' item from DUAL UNION ALL  SELECT 'toys,toys,toys' item from DUAL UNION ALL  SELECT 'toys,toys,book,toys' item from DUAL   )  SELECT item from t where not REGEXP_LIKE(item||',', '[^toys,]')

You seem to misunderstand negated character classes. [^toys,] matches any SINGLE character that is not a t, not an o, a y, an s or a comma. As long as ITEM only contains the letters t, o, y, s and comma - in any order and forming any words - the ITEM will be selected.

So for example:

WITH T AS    
(SELECT 'tootsy,soy' item from DUAL)    
SELECT item  
from t  
where not REGEXP_LIKE(item||',', '[^toys,]');
ITEM
--------
tootsy,soy

Solomon Yakobson

mathguy wrote:

I don't think this is right either.

Correct, we must append comma at the end and replace 'toys,' like I did in my earlier reply and also check if original string is not null.

SY.

mathguy

Understood. I just didn't see where it was pointed out explicitly that the other poster's attempt was incorrect.

Alas, very often in this forum wrong answers go unchallenged, and too often such answers are even selected as the Correct Answer or marked Helpful. It annoys me to no end when that happens, especially after it was pointed out already that the answer is wrong, but I can't do anything about that (other than to point out to the OP how lame that is - I have done it several times). I can, though, point out explicitly the wrong answers, with examples, as I have been doing since I joined this forum.

Paulzip

Solomon Yakobson wrote:
First you need to write regexp_like that selects rows containing nothing but comma-separated set of word 'toys'
where regexp_like (item, '(^|,)toys(,|$)', 'i')
doesn't do that.
SY.

Good point! I obviously didn't read the post correctly.

mathguy

Maybe "third time's a charm", as they say. I already posted two answers, only to delete them later after I figured out they were wrong.

Here is a correct way (I think) to use RPAD for this job. Note that the first condition is needed, to exclude inputs like 'toys,to'

select item
from   t
where  mod(length(item) + 1, length('toys') + 1) = 0 and item = rpad('toys', length(item), ',toys')
;

mathguy

Well after I pointed out the wrong answers, the OP marked as "correct" his own version of the answer proven in Reply 20 to be incorrect, and marked as "helpful" the answer I showed in Reply 21 to be incorrect. How is that for proving my point?

1 - 26

Added on Nov 1 2018

#sql-pl-sql

26 comments

7,310 views

SQL & PL/SQL

How to check value in a comma delimiter in sql query

Comments

Post Details