Neha Neha - 7 months ago 194
SQL Question

Convert comma seperated string to a list

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 seperated string

ie. 1234, 2345, 3456, 4567

SELECT *
FROM tbl_Enterprise
WHERE Enterprise_ID IN ( Path )


My database is Vertica.

Any help would be appreciated :)
Thanks!

Answer

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)