SQL SERVER 2014 - 拆分字符串并转换为 INT

Question

我有一个字符串

1,2,3|5

用 | 拆分后我有

1,2,3, 5

现在我想将 1,2,3 转换为 INT。我怎样才能做到这一点？

以下是我的查询

Declare @tmpReferContentRule = "1,2,3|5"
SELECT items 
FROM splitbystring(@tmpReferContentRule,'|') 
WHERE id = 1

我想用在

SELECT name 
FROM tmptable 
WHERE id in(SELECT items FROM splitbystring(@tmpReferContentRule,'|'))

_{id 是整数类型}

上面的查询抛出一个错误

1,2,3 could not be converted to int.

请提出解决方案。

Answer 1

你可以试试这个-

Declare @tmpReferContentRule varchar(20) = '1,2,3|5'

;with cte
AS
(
    SELECT items as subitem from dbo.splitbystring(@tmpReferContentRule,'|')
)
SELECT name FROM tmptable WHERE id IN 
(
    SELECT t.items FROM cte
    CROSS APPLY (SELECT * from dbo.splitbystring (subitem,',')) t
)