SQL 拼接字符串 使用IN查询方法
程序员文章站
2022-07-05 10:42:36
问题描述 当在 SQL SERVER 中查询的时候,同事遇到一个字段存储的字符串为用逗号分隔的主键 ID 值,格式为:1,2,3,4,这时候需要查询符合条件的所有数据,所以选择使用 IN 查询,但是直接执行会提示错误:在将 varchar 值 '1,2,3,4' 转换成数据类型 int 时失败。于是 ......
问题描述
当在 sql server 中查询的时候,同事遇到一个字段存储的字符串为用逗号分隔的主键 id 值,格式为:1,2,3,4,这时候需要查询符合条件的所有数据,所以选择使用 in 查询,但是直接执行会提示错误:在将 varchar 值 '1,2,3,4' 转换成数据类型 int 时失败。于是咨询我怎么解决呢?
我看了下错误信息,很明显是因为 in 的字段是一个 int 类型,强制把 varchar '1,2,3,4' 转换成 int 语法是不允许的,所以查询失败。如下解决思路告诉我了同事。
解决思路
如果要使用 in 就必须把当前字符串 “列的形式” 转换成 “行的形式” 语法上才行,也就是说把 '1,2,3,4' 的字符串转换成一个只有一列的表才行,表中有4行数据分别存储1,2,3,4,这时候就可以正确使用 in 关键字来了。那如何解决就不报错还能查询数据呢?解决利器是 sql server 中表值函数(可以理解是一个有 table 返回值的方法)
在 sql 中位置在 数据库名称 --> 可编程性 --> 表值函数
告诉同事按照这个思路试着写下,我随后也写了一个通用的表值函数给他,废话不多说,直接上关键代码。
关键代码
create function [dbo].[f_substringintarray] ( @str varchar(1000), --字符串队列格式为:1,2,3,4 @char varchar(10) --截取字符串的符号:, ) returns @result table(id int) as begin declare @index int --声明截取符号的位置 declare @orderid int --声明获取的int编号 --判断传递的字符串不能为空 if len(@str) > 0 begin --查询第一个符号,的位置 set @index = charindex(@char, @str) --循环截取字符串 while @index > 0 begin --截取第一个,位置的值 set @orderid = substring(@str, 1, @index - 1) --截取@str字符串第一个值,截取后变成 2,3,4 set @str = substring(@str, @index + 1, len(@str) - @index) --重新给@index赋值 set @index = charindex(@char, @str) --插入到返回的表格中 insert into @result(id) values(@orderid) end --判断是否截取到最后一个 if @index = 0 and len(@str) > 0 begin --插入到返回的表格中 insert into @result(id) values(@str) end end return end /* 测试当前的函数 --第一种情况 select * from [dbo].[f_substringintarray]('1,2,3', ',') --第二种情况 select * from [dbo].[f_substringintarray]('1', ',') */ go
完美解决问题!我很开心能帮到他,同时也得到了一个大大的