求一条单表查询的复杂SQL语句？？
表T1，有两个字段 fsNums,fsAlreadyNums
假设有如下两条记录
fsNums 1001,1002,1003-1008
fsAlreadyNums 1001
fsNums 1009，1010-1020
fsAlreadyNums 1015
假设：现有一个未知编号，假如是：1002
因为1002，属于第一条记录fsNums字段所包含的值，要写一条Sql语句，更改第一条语句fsAlradyNums的值为1001,1002
假设：现有一个未知编号，假如是：1012
因为1012,属于第二条记录fsNums字段所包含的值(1010-1020)；这条语句，要更改第二条语句fsAlradyNums的值为1015,1012
语句类似如下
update T1 set fsAlreadyNums=fsAlreadyNums+","+1012 where fsNums like '%1012%' or .................????????
[解决办法]
Create function IsInCardString_T(@CardString varchar(8000),@CardNo varchar(4))returns intasbegin declare @temp table(a varchar(200)) declare @i int set @CardString = rtrim(ltrim(@CardString))+',' set @i = charindex(',', @CardString) while @i >= 1 begin insert @temp values(left(@CardString, @i - 1)) set @CardString = substring(@CardString, @i + 1, len(@CardString) - @i) set @i = charindex(',', @CardString) end if exists(select 1 from ( select case when charindex('-',a) > 0 then left(a,4) else a end as s, case when charindex('-',a) > 0 then right(a,4) else a end as e from @temp ) a where @CardNo between s and e) set @i= 1 else set @i= 0 return @ienddeclare @表T1 table (fsNums varchar(20),fsAlreadyNums varchar(10))insert into @表T1select '1001,1002,1003-1008','1001' union allselect '1009,1010-1020','1015'declare @p varchar(10) set @p=1002update @表T1 set fsAlreadyNums=fsAlreadyNums+','+@pwhere dbo.IsInCardString_T(fsNums,@p)=1select * from @表T1/*fsNums fsAlreadyNums-------------------- -------------1001,1002,1003-1008 1001,10021009,1010-1020 1015*/declare @表T2 table (fsNums varchar(20),fsAlreadyNums varchar(10))insert into @表T2select '1001,1002,1003-1008','1001' union allselect '1009,1010-1020','1015'declare @q varchar(10) set @q='1012'update @表T2 set fsAlreadyNums=fsAlreadyNums+','+@qwhere dbo.IsInCardString_T(fsNums,@q)=1select * from @表T2/*fsNums fsAlreadyNums-------------------- -------------1001,1002,1003-1008 10011009,1010-1020 1015,1012*/