将逗号分隔的字符串转换为列表 [英] Convert comma separated string to a list

问题描述

我想传递一个整数列表(逗号分隔)，这是我表中的一个字段

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

即.1234、2345、3456、4567

ie. 1234, 2345, 3456, 4567

到我在 WHERE 中的 IN 子句.但该列表是一个字符串 (VARCHAR)，我将其与 int 字段进行比较.有没有办法将列表转换为整数列表?

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 是 INT
路径是表中的一个字段，它是一个逗号分隔的字符串

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

即.1234、2345、3456、4567

ie. 1234, 2345, 3456, 4567

SELECT *
FROM tbl_Enterprise
WHERE Enterprise_ID IN ( Path )

我的数据库是 Vertica.

My database is Vertica.

推荐答案

您可以在 vertica 中使用 SPLIT_PART 函数将逗号分隔的列表拆分为行并将它们插入到临时表中.使用类似这样的查询来实现您的目标:

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 )

分部功能:https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/SPLIT_PART.htm

以下是使用 split_part 将字符串拆分为行的示例:

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)

这篇关于将逗号分隔的字符串转换为列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！