Geek Answers Handbook

Convert comma separated string to list

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

t. 1234, 2345, 3456, 4567

in my IN WHERE in WHERE . But the list is a string ( VARCHAR ), and I compare this to an int field. Is there a way to convert a list to an ints list?

Enterprise_ID - INT
A path is a field in a table that is a comma separated string

t. 1234, 2345, 3456, 4567

 SELECT * FROM tbl_Enterprise WHERE Enterprise_ID IN ( Path )

My database is Vertica.

+6
source share
4 answers

You can use the SPLIT_PART function in Vertica to split a comma-separated list into rows and insert them into the temp table. To achieve your goal, use the following query:

 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 an example of splitting a string into strings 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)
+5
source

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

The first method uses the functions that are included 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 the functions included 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)
+3
source

A slightly improved version of the solution proposed by Abney:

 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
0
source

String concatenation is a difficult task, so I suggest you avoid it. Since you decided to save them as a string and not create a parent-child table, I suggest you save them this way, 1234.2345.3456.4567, (add at the beginning and end of your value and trim all spaces).

Then you can easily do a search using sql, for example:

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

More articles:


All Articles