如何将int列值转换为nvarchar在sql server数据库中的where条件？

问题描述

我需要在逗号分隔的userid'列表中找出单个用户ID，但问题是userid是整数，而userid'的列表是nvarchar，因为它包含逗号分隔列表因此它显示以下错误



将nvarchar值''11，18'转换为数据类型int时转换失败。



请告诉我该怎么办。

thnx提前。

I need to find out single userid within a comma separated list of userid''s but the problem is that userid is integer and the list of userid''s is in nvarchar as it contains comma separated list so therefore it is showing the following error

Conversion failed when converting the nvarchar value ''11,18'' to data type int.

Please let me know how can I do it.
thnx in advance.

推荐答案

DECLARE @tbl TABLE(Ids VARCHAR(50))
INSERT INTO @tbl (Ids)
VALUES('1,2,3,5')
INSERT INTO @tbl (Ids)
VALUES('7,11,12,15,33')
INSERT INTO @tbl (Ids)
VALUES('9,10,22,35,43')
INSERT INTO @tbl (Ids)
VALUES('13,19,32,45,53')


;WITH cteIDs AS
(
	SELECT ROW_NUMBER() OVER(ORDER BY Ids) AS RowNo, CONVERT(INT, LEFT(Ids, CHARINDEX(',',Ids)-1)) AS Id, RIGHT(Ids,LEN(Ids)-CHARINDEX(',',Ids)) AS Remainder
	FROM @tbl
	WHERE CHARINDEX(',',Ids)>0
	UNION ALL
	SELECT RowNo, CONVERT(INT, LEFT(Remainder, CHARINDEX(',',Remainder)-1)) AS Id, RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder)) AS Remainder
	FROM cteIds
	WHERE CHARINDEX(',',Remainder)>0
	UNION ALL
	SELECT RowNo, Remainder AS Id, NULL AS Remainder
	FROM cteIds
	WHERE CHARINDEX(',',Remainder)=0
)
SELECT *
FROM cteIds
ORDER BY Id, RowNo

结果：

Result:

RowNo   Id      Remainder
1	1	2,3,5
1	2	3,5
1	3	5
1	5	NULL
3	7	11,12,15,33
4	9	10,22,35,43
4	10	22,35,43
3	11	12,15,33
3	12	15,33
2	13	19,32,45,53
3	15	33
2	19	32,45,53
4	22	35,43
2	32	45,53
3	33	NULL
4	35	43
4	43	NULL
2	45	53
2	53	NULL

这篇关于如何将int列值转换为nvarchar在sql server数据库中的where条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！