CODEWITHSUNDEEP
sqlamazon-redshift
dark horse
dark horse

Reputation: 3719

Redshift - Filter out values without comma in row

I have a column that has values with commas (,). I am trying to filter out rows that do not have a comma.

Given below is a sample view of my dataset

id,name,product_list
1,kevin,prod_a
2,scott,prod_b,prod_c
3,tim,prod_a
4,julie,prod_a,prod_e

I am expecting the SQL to return only ids (1,3) as they do not have a comma in product_list column. Could anyone help me on how could I filter the above data set.

I am using Redshift DB.

Upvotes: 0

Views: 239

Answers (2)

Nav
Nav

Reputation: 1394

Multiple ways of doing it and one is already suggested, then another could be:

select * from test where len(product_list)-len(REPLACE(product_list,',',''))<1;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can use like operator 

select * from table1 where product_list NOT LIKE '%,%'

http://sqlfiddle.com/#!9/a081d/1

id  name    productlist
1   kevin   prod_a
3   tim     prod_a

Upvotes: 5

Related Questions