如何将int列值转换为nvarchar在sql server数据库中的where条件？ [英] How to convert int column value into nvarchar in where condition in sql server database?

问题描述

我需要在逗号分隔的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.

推荐答案

你可以用 CTE [ ^ ]。

You can achieve that using CTE[^].

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

您好，



您可以使用UDF（用户定义函数）进行拆分过程转换它会起作用。

查看帖子...

拆分功能错误 - 声明终止。在语句完成之前，最大递归100已经耗尽 [ ^ ]

问候，

GVPrabu

Hi,

U can you UDF (User Define Functions) for Split Process then Convert it will works.
Check the post...
Split function error - The statement terminated. The maximum recursion 100 has been exhausted before statement completion[^]
Regards,
GVPrabu

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