将varchar值转换为数据类型int时转换失败 [英] Conversion failed when converting the varchar value to data type int

问题描述

我有一个表分支和另一个表pincode，其中一列可以包含用逗号分隔的pincode ID作为varchar数据类型。我想根据分支ID选择相关的密码，但我收到错误转换varchar值'1,3,5'时转换失败，'到数据类型int。当试图选择：



来自tblpin的SELECT Pincode，其中pinID在（从分支中选择pinid，其中brnchID ='1'）;



子查询返回的数据类似于1,3,5

I have table branch and an another table pincode with a column which can contain pincode IDs separated with comma as varchar data type. I want to Select related pincodes based on branch id but I'm getting error "Conversion failed when converting the varchar value '1,3,5,' to data type int." when trying to select:

SELECT Pincode from tblpin where pinID in (select pinid from branch where brnchID='1');

The subquery returns data like "1,3,5"

推荐答案

你可以这样做：SQL deos不懂逗号分隔数据除了特定的地方 - 而数据列值不是其中之一。

这是可能的 - 但是很痛苦：在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]



一个更好，更好的解决方案是不将ID存储为字符串，而是设置一个单独的表来链接它们。

You can;t do it like that: SQL deos not understand comma separated data except in specific places - and a data column value is not one of them.
It's possible - but a pain: Using comma separated value parameter strings in SQL IN clauses[^]

A much, much better solution is not to store ID as strings at all, but set up a separate table linking them instead.

这篇关于将varchar值转换为数据类型int时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！