I have table categories (c) and an another table (x) with a column which can contain cat IDs separated with comma as varchar data type. I want to Select related categories but I'm having error "Conversion failed when converting the varchar value '5,' to data type int." when trying to select:
我有表类别(c)和另一个表(x),其中的列可以包含用逗号分隔的cat id作为varchar数据类型。我想选择相关的类别,但在将varchar值'5 '转换为数据类型int时,我的转换失败了。
SELECT ID, Title FROM c WHERE ID IN (SELECT catIDs FROM x WHERE ID=any);
The subquery returns data like "1,3,4"
子查询返回数据,如“1,3,4”
2 个解决方案
#1
4
You need to split the 1,3,4
string returned by the subquery into separate int
values. SQL Server does not have a built-in function to do it, but you can use this user-defined function.
您需要将子查询返回的1,3,4字符串分割为单独的int值。SQL Server没有内置的函数来实现它,但是您可以使用这个用户定义的函数。
Create the function dbo.Split
in your database and then re-write your query as follows:
dbo创建函数。在数据库中分割,然后重新编写查询,如下:
SELECT ID, Title
FROM c
WHERE ID IN
(
SELECT s
FROM dbo.Split(',', '1,3,4')
)
I replaced the subquery with example results 1,3,4
to shorten the query and make it easier to understand.
我将子查询替换为示例结果1,3,4,以缩短查询并使其更容易理解。
#2
1
If I get it right, you actually have values like "1,3,4" in your column catIDs. So you should extract a substring in the select of your subquery.
如果我写对了,你的列catIDs中就会有“1、3、4”这样的值。因此,您应该在子查询的select中提取子字符串。
By the way, I'm not an MS SQL Server expert, but it's probably a bad database design to do so. I'm not sure the RDBMS engine will use indexes in such a case...
顺便说一下,我不是MS SQL Server专家,但是这样做可能是一个糟糕的数据库设计。我不确定RDBMS引擎是否会在这种情况下使用索引……
#1
4
You need to split the 1,3,4
string returned by the subquery into separate int
values. SQL Server does not have a built-in function to do it, but you can use this user-defined function.
您需要将子查询返回的1,3,4字符串分割为单独的int值。SQL Server没有内置的函数来实现它,但是您可以使用这个用户定义的函数。
Create the function dbo.Split
in your database and then re-write your query as follows:
dbo创建函数。在数据库中分割,然后重新编写查询,如下:
SELECT ID, Title
FROM c
WHERE ID IN
(
SELECT s
FROM dbo.Split(',', '1,3,4')
)
I replaced the subquery with example results 1,3,4
to shorten the query and make it easier to understand.
我将子查询替换为示例结果1,3,4,以缩短查询并使其更容易理解。
#2
1
If I get it right, you actually have values like "1,3,4" in your column catIDs. So you should extract a substring in the select of your subquery.
如果我写对了,你的列catIDs中就会有“1、3、4”这样的值。因此,您应该在子查询的select中提取子字符串。
By the way, I'm not an MS SQL Server expert, but it's probably a bad database design to do so. I'm not sure the RDBMS engine will use indexes in such a case...
顺便说一下,我不是MS SQL Server专家,但是这样做可能是一个糟糕的数据库设计。我不确定RDBMS引擎是否会在这种情况下使用索引……