欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL 拼接字符串 使用IN查询方法

程序员文章站 2022-03-20 20:58:16
问题描述 当在 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 时失败。于是咨询我怎么解决呢?

SQL 拼接字符串 使用IN查询方法

我看了下错误信息,很明显是因为 in 的字段是一个 int 类型,强制把 varchar '1,2,3,4' 转换成 int 语法是不允许的,所以查询失败。如下解决思路告诉我了同事。

 

解决思路

如果要使用 in 就必须把当前字符串 “列的形式” 转换成 “行的形式” 语法上才行,也就是说把 '1,2,3,4' 的字符串转换成一个只有一列的表才行,表中有4行数据分别存储1,2,3,4,这时候就可以正确使用 in 关键字来了。那如何解决就不报错还能查询数据呢?解决利器是 sql server 中表值函数(可以理解是一个有 table 返回值的方法)

在 sql 中位置在 数据库名称 --> 可编程性 --> 表值函数

SQL 拼接字符串 使用IN查询方法

告诉同事按照这个思路试着写下,我随后也写了一个通用的表值函数给他,废话不多说,直接上关键代码。

 

关键代码

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

完美解决问题!我很开心能帮到他,同时也得到了一个大大的