一、查询结果使用，字符串拼接
declare @names nvarchar(1000)
declare @ParmDefinition nvarchar(1000)
declare @sqltext nvarchar(500)
set @sqltext=N'Select @names=isnull(@names + '','' , '''' ) + isnull(列名, '''' ) From 表名'
set @ParmDefinition = N'@names nvarchar(1000) OUTPUT';
exec sp_executesql
@sqltext,
@ParmDefinition,
@names=@names OUTPUT
select @names as Res
二、SQL函数 获取“，”字符串长度
Create function [dbo].[GetSplitLength]
(
@String nvarchar(max), --要分割的字符串
@Split nvarchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int set @String=ltrim(rtrim(@String))
set @location=charindex(@split,@String)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@String,@start)
set @length=@length+1
end
return @length
end
三、SQL函数 遍历“，”拼接的字符串
create function [dbo].[GetSplitOfIndex]
(
@String nvarchar(max), --要分割的字符串
@split nvarchar(10), --分隔符号
@index int --取第几个元素
)
returns nvarchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int set @String=ltrim(rtrim(@String))
set @start=1
set @next=1
set @seed=len(@split) set @location=charindex(@split,@String)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@String,@start)
set @next=@next+1
end
if @location =0 select @location =len(@String)+1 return substring(@String,@start,@location-@start)
end
四、FOR XML PATH 使用
SELECT 列名+',' FROM 表名 FOR XML PATH('')
https://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html
五、IN 参数查询
create function [dbo].[f_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end
这样就可以直接使用IN来查询了 查询语句如下：
select schoolname from school_info where code in(select col from [dbo].[f_split](@area,','))