Convert comma separated string to a list

Question

I want to pass a list of int's (comma separated) which is a field in my table

ie. 1234, 2345, 3456, 4567

to my IN clause in WHERE. But the list is a string (VARCHAR), and I'm comparing to an int field. Is there a way for me to convert the list to list of ints?

Enterprise_ID is INT
Path is a field in the table which is a comma separated string

ie. 1234, 2345, 3456, 4567

SELECT *
FROM tbl_Enterprise
WHERE Enterprise_ID IN ( Path )

My database is Vertica.

Katiakatie

Do you have control over the database structure? A list of comma-separated integers in a column is a strong hint that your database structure is wrong. – Krawczyk

@MattGibson I think the actual problem is what he is passing as parameter to the query (varchar list of comma separated numbers), not the DB structure. – Priorate

@RaduGheorghiu Storing multiple values in a column is a problem of the structure. – Okwu

@Okwu OP didn't mention anything about multiple values being stored in a single column (which I agree, is a problem of the structure) - "But the list is a string (VARCHAR), and I'm comparing to an int field" – Priorate

@RaduGheorghiu I see your interpretation. I think you're right! – Okwu

Answer 1

You can use SPLIT_PART function in vertica to split the comma separated list into rows and insert them into a temp table. Use a query something like this to achieve your goal:

SELECT * FROM tbl_Enterprice WHERE Enterprice_ID IN ( Select Enterprice_ID from temp_table )

Split part function: https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/SPLIT_PART.htm

Here is a example of splitting string into rows using split_part:

dbadmin=> SELECT SPLIT_PART('JIM|TOM|PATRICK|PENG|MARK|BRIAN', '|', row_num) "User Names"
dbadmin->   FROM (SELECT ROW_NUMBER() OVER () AS row_num
dbadmin(>           FROM tables) row_nums
dbadmin->  WHERE SPLIT_PART('JIM|TOM|PATRICK|PENG|MARK|BRIAN', '|', row_num) <> '';
 User Names
------------
 JIM
 TOM
 PATRICK
 PENG
 MARK
 BRIAN
(6 rows)

Raggletaggle

Thanks for your suggestion, but Split_Part is used to get one particular part of the string. How can I use split_part to get all the IDs seperated by comma in rows? – Katiakatie

I have added an example for splitting string into rows using split_part. Please tweak it to suit your use. Hope this helps!! – Raggletaggle

Answer 2

I would consider these two solutions to be anti-patterns and would recommend testing them for performance.

The first method uses functions that come in the flex table package.

SELECT values::INT as var1
FROM (
    SELECT MapItems(v1) OVER () AS (keys, values)
    FROM (
        SELECT MapDelimitedExtractor( '1234, 2345, 3456, 4567' 
                                       USING PARAMETERS DELIMITER=',') AS v1
    ) AS T
) AS T2
WHERE REGEXP_SUBSTR(values,'\d+',1) IS NOT NULL;
 var1 
------
 1234
 2345
 3456
 4567
(4 rows)

The second method uses functions that comes in the text index package.

SELECT words::INT AS var1 
FROM (
    SELECT TxtIndex.StringTokenizerDelim('1234, 2345, 3456, 4567',',') 
           OVER() AS (words, input_string)
) AS T
WHERE REGEXP_SUBSTR(words, '\d+',1) IS NOT NULL;
 var1 
------
 1234
 2345
 3456
 4567
(4 rows)

Claqueur

Answer 3

Slightly improved version of solution suggested by Abnay:

SELECT SPLIT_PART('JIM|TOM|PATRICK|PENG|MARK|BRIAN|AAA', '|', row_num) "User Names"
FROM (SELECT ROW_NUMBER() OVER () AS row_num FROM columns) row_nums
WHERE  REGEXP_COUNT('JIM|TOM|PATRICK|PENG|MARK|BRIAN|AAA', '\|') + 2 > row_num

Empennage

Answer 4

Why not use find_in_set

SELECT *

FROM tbl_Enterprise WHERE FIND_IN_SET(Enterprise_ID, Path)

Romie

Answer 5

We can use find_in_set but it does not use index. Here is a trick that would use index

SELECT t1.* 
FROM tbl_Enterprise t1
INNER JOIN path_table t2
INNER JOIN (
    SELECT * FROM (SELECT  @n:=@n+1 AS n FROM tbl_Enterprise INNER JOIN (SELECT @n:=0) AS _a) AS _a WHERE _a.n <= 20
) 
AS k ON k.n <= LENGTH(t2.path_field) - LENGTH(replace(t2.path_field, ',','')) + 1
and t1.id = SUBSTRING_INDEX(SUBSTRING_INDEX(t2.path_field, ',', k.n), ',', -1)

20 is the maximum number of values in comma separated list.

This is a trick to extract nth value in comma separated list:

SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', k.n), ',', -1)

Mcgaha

Answer 6

String concatenation is a heavy task, so I would propose you to avoid it. Since you decided to save them as a string and not to create a "parent-child" tables, I propose you to save them this way ,1234,2345,3456,4567, (add a , at the beginning and end of your value and trim all spaces).

Then you could easily search with sql, for example:

SELECT * FROM tbl_Enterprice WHERE Enterprice_ID like ('%,your_value,%')

Peneus