- ----------------------------
- --Author:旋风
- --Date:2008-10-23 15:19:49
- --Version:V1.0
- --Memo:SQL替换字段字符串
- ----------------------------
- --> Test Data: [A]
- if object_id('[A]') is not null drop table [A]
- create table [A] ([AutoID] int,[IDStr] varchar(50))
- insert into [A]
- select 1,'1,2,3,4' union all
- select 2,'1,2'
- --> Test Data: [B]
- if object_id('[B]') is not null drop table [B]
- create table [B] ([AutoID] int,[Value] varchar(3))
- insert into [B]
- select 1,'AAA' union all
- select 2,'BBB' union all
- select 3,'CCC' union all
- select 4,'DDD'
- Go
- if object_id('S_str') is not null
- drop function S_str
- go
- create function S_str(@str nvarchar(100))
returns nvarchar(100)
as
begin
set @str=','+@str+','--加上两边逗号
select @str=replace(@str,','+rtrim([AutoID])+',',','+[Value]+',') from B
--where @str like '%,'+rtrim([AutoID])+',%'
WHERE CHARINDEX(','+RTRIM([AutoID])+',',@str)>0
return substring(@str,2,len(@str)-2)--去掉两边逗号
end
go
--Code
--SQL2000用函数
select [AutoID],[Value]=dbo.S_str([IDStr]) from A - --SQL2005用XML
- SELECT [AutoID],
- [Value]=CAST(STUFF((
- SELECT ','+[Value]
- FROM B
- WHERE CHARINDEX(','+RTRIM([AutoID])+',',','+a.[IDStr]+',')>0
- FOR XML PATH(''))
- ,1,1,'') AS VARCHAR(20))
- FROM A AS a
- --Drop
- drop table [A]
- drop function S_str
- --Result
- /*
- AutoID Value
- ----------- ----------------------------------------------------------------------------------------------------
- 1 AAA,BBB,CCC,DDD
- 2 AAA,BBB
- */
SQL替换字段字符串
最新推荐文章于 2023-10-18 17:08:51 发布