Split a Column with Delimited Values and Compare Each Value

1
votes

I have a column that contains multiple values in a delimited(comma-separated) format -

id | code
------------
1   11,19,21
2   55,87,33
3   3,11
4   11

I want to be able to compare to each value inside the 'code' column as below -

SELECT id FROM myTbl WHERE code = '11'

This should return -

1
3
4

I've tried the solution below but it does not work for all cases -

SELECT id FROM myTbl WHERE POSITION('11' IN code) <> 0

This will work with a 2 digit number like '11' as it will return a value that is <> 0 if it finds a match. But it will fail when searching for say '3' because rows with 'id' 2 and 3 both will be returned.

Here is link that talks about the POSITION function in REDSHIFT.

Any other approach that will solve this problem?

4
sqlsplitamazon-redshift
You shouldn't be storing comma delimited values in a single column to begin with. - a_horse_with_no_name
This happened because the table is rolledup/aggregated to a level and we didn't want to lose information from a column - sandunes90
@sandunes90 . . . You have taken the wrong approach to aggregating your data. - Gordon Linoff

4 Answers

0
votes

you can get the count of this string

SELECT id FROM myTbl WHERE regexp_count(user_action, '[11]') > 0
0
votes

I think we can use regexp_substr() as follow. 

select tb .id from myTbl tb where '11' in (
    select regexp_substr( (select code from myTbl where id=tb.id),'[^,]+', 1, LEVEL) from dual
    connect by regexp_substr((select code from myTbl where id=tb.id) , '[^,]+', 1, LEVEL) is not null);

just try this.

0
votes

Use split_part() function

SELECT distinct id 
  FROM myTbl 
 WHERE '11' in ( split_part( code||',' , ',', 1 ), 
                 split_part( code||',' , ',', 2 ), 
                 split_part( code||',' , ',', 3 ) )
0
votes

This is a very, very bad data model. You should be storing this information in a junction/association table, with one row per value.

But, if you have no choice, you can use like:

SELECT id
FROM myTbl
WHERE ',' || code || ',' LIKE '%,11,%';