SQLSERVER 拆分拼接字符串-优快云博客

本文介绍如何在SQL Server中使用WHILE循环拆分和打印字符串，以及如何通过动态SQL将字符串数组转换为数据库查询。同时展示了如何利用T-SQL生成带有链接的XML文档。

SQLSERVER 拆分拼接字符串，WHILE循环输出

 1 DECLARE @Items VARCHAR(1000)
 2 SET @Items='148,149,150,151' +',' 
 3 DECLARE @ItemIndex INT
 4 set @ItemIndex=CHARINDEX(',',@Items)
 5  
 6 WHILE(@Items!=',' AND @ItemIndex>0)
 7 BEGIN
 8     PRINT LEFT(@Items,@ItemIndex-1)
 9     SET @Items=RIGHT(@Items,LEN(@Items)-@ItemIndex)
10     SET @ItemIndex=CHARINDEX(',',@Items)
11 END

拆分字符串并插入临时表

declare @s varchar(100),@sql varchar(1000)
set @s='1,2,3,4,5,6,7,8,9,10'
set @sql='select col='''+ replace(@s,',',''' union all select ''')+''''
PRINT @sql
exec (@sql)

附件配置显示文档并下载

(SELECT  '<a  href="../' + ATTACH.FILEPATH + '"  aid="'+ CAST(ATTACH.AID AS VARCHAR) + '" aname="' + ATTACH.FILETILE+ '"  atype="' + ATTACH.FILETYPE + '">' + ATTACH.FILETILE+ '</a><br/>'
 FROM    dbo.SYS_ATTACHMENT ATTACH
 WHERE   CHARINDEX(',' + CAST(ATTACH.AID AS VARCHAR) + ',',',' + a.FILES + ',') > 0 FOR  XML PATH('') ,TYPE).value('.', 'NVARCHAR(MAX)') 
 AS FILESNAME