0

I have a table called DUMMY_TAB which has a column COLOR which contains comma separated values as RED,BLUE,WHITE,GREEN,YELLOW. Now i want to check whether multiple colors are present or not and i don't know the order in which values are stored.

I have used query like:

SELECT COLOR from DUMMY_TAB WHERE (COLOR LIKE '%GREEN%' OR COLOR LIKE '%VOILET%' OR COLOR LIKE '%ORANGE%');

but query is not returning anything. can i get any help here.

Imran
  • 429
  • 9
  • 23
  • 2
    Aside from the fact that storing a comma-separated list in a column is horrible practice that violates basic normalization, if your table contained data as you describe, the query you posted would return that row. If the query isn't returning a row, the data must not contain the string `GREEN`. Perhaps the data in your table is lower case while you're searching for an upper-case value? – Justin Cave Sep 25 '14 at 11:17
  • My row has upper case comma separated values same as of which ii have asked in my question. like GREEN,BLUE,YELLOW – Imran Sep 25 '14 at 11:21
  • If the data and the query are exactly as you show them, the query would return a row. Show us a self-contained example the replicates your problem that we can run on our machines. Otherwise, it's nearly impossible to debug what the problem might be – Justin Cave Sep 25 '14 at 11:26

2 Answers2

1

You should be using a junction table. SQL has this great data structure for storing lists. It is called a table. Comma separated values are not the right method.

But, sometimes, you have no choice. You are looking for:

where ',' || 'GREEN' || ',' like ',' || COLOR || ','

The use of delimiters ensures that you can find the value at the beginning and end of the list.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You are certainly violating normalization as highlighted by Justin Cave.

Also the best practice is to store them in columns of a table and not all in one.

I could not replicate your problem statement. See proof here

create table temp (col varchar2(30));

insert into temp values('RED,BLUE,WHITE,GREEN,YELLOW');

SELECT * from temp WHERE col LIKE '%GREEN%';

COL
RED,BLUE,WHITE,GREEN,YELLOW
Srini V
  • 11,045
  • 14
  • 66
  • 89