sql server replace函数巧妙完成字符串联结

示例一:

需求:将'1,2,3,4,5,6,7,8,9,10'转成:'1','2','3','4','5','6','7','8','9','10'

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

显示结果:

'1','2','3','4','5','6','7','8','9','10'

 

示例二:

需求:将'S1,S2,S3' 转成:isnull(S1,0)+isnull(S2,0)+isnull(S3,0)

declare 
@s varchar(500),
@Siz3Sql10 varchar(500)
set @s='S1,S2,S3'
set @Siz3Sql10='isnull('+replace(@s,',',',0)+isnull(')+',0)'

select @Siz3Sql10

 

示例三:

需求:在SSRS报表里将"4,5,6",转换成:[就诊科室].[Id],&[4],[就诊科室].[Id],&[5],[就诊科室].[Id],&[6]

 ="[就诊科室].[Id],&[" & Replace("4,5,6",",","],[就诊科室].[Id],&[") & "]" 
posted @ 2014-11-12 10:11  Rain520  阅读(421)  评论(0编辑  收藏  举报