USE test GO -->生成表t1 --if object_id('t1') is not null -- drop table t1 --Go --Create table t1([id] smallint,[Uid] nvarchar(10),[UserID] smallint) --Insert into t1 --Select 1,',60,61,66,',127 --Union all Select 2,',60,61,',130 --Union all Select 3,',66,67,',128 --Union all Select 4,',68,',129 DECLARE @sql NVARCHAR(MAX),@UserID SMALLINTSET @UserID=127 SELECT @sql=ISNULL(@sql+' UNION ALL ','')+'SELECT '+LTRIM(UserID)+' As UserID,N'''+REPLACE(REPLACE('%'+REPLACE(Uid+'%',',%',''),'%,',''),',',''' As Uid UNION ALL SELECT '+LTRIM(UserID)+' As UserID,''')+''' As Uid' from t1SET @sql='SELECT Uid FROM ('+@sql+') t Where t.UserID='+LTRIM(@UserID)+' Order by Uid' EXEC (@Sql) GO
declare @t1 table ([id] smallint,[Uid] nvarchar(10),[UserID] smallint) Insert into @t1 Select 1,',60,61,66,',127 Union all Select 2,',60,61,',130 Union all Select 3,',66,67,',128 Union all Select 4,',68,',129 declare @i int set @i='127' declare @a varchar(2) declare @b varchar(2) declare @c varchar(2) update @t1 set Uid=RIGHT(uid,len(uid)-CHARINDEX(',',uid) ) where Userid=@i select @a=left(uid,CHARINDEX(',',uid)-1) from @t1 where Userid=@i update @t1 set Uid=RIGHT(uid,len(uid)-CHARINDEX(',',uid) ) where Userid=@i select @b=left(uid,CHARINDEX(',',uid)-1) from @t1 where Userid=@i update @t1 set Uid=RIGHT(uid,len(uid)-CHARINDEX(',',uid) ) where Userid=@i select @c=left(uid,CHARINDEX(',',uid)-1) from @t1 where Userid=@i update @t1 set Uid=RIGHT(uid,len(uid)-CHARINDEX(',',uid) ) where Userid=@i select @a as uid union select @b union select @c只针对这题...
去查查字符串拆分的例子,比较常用的就是利用master..spt_values这个表。
给你个分拆列值的例子有表tb, 如下: id value ----------- ----------- 1 aa,bb 2 aaa,bbb,ccc 欲按id,分拆value列, 分拆后结果如下: id value ----------- -------- 1 aa 1 bb 2 aaa 2 bbb 2 ccc1. 旧的解决方法(sql server 2000) SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id) FROM tb A, # B WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #
GO
-->生成表t1
--if object_id('t1') is not null
-- drop table t1
--Go
--Create table t1([id] smallint,[Uid] nvarchar(10),[UserID] smallint)
--Insert into t1
--Select 1,',60,61,66,',127
--Union all Select 2,',60,61,',130
--Union all Select 3,',66,67,',128
--Union all Select 4,',68,',129
DECLARE @sql NVARCHAR(MAX),@UserID SMALLINTSET @UserID=127
SELECT @sql=ISNULL(@sql+' UNION ALL ','')+'SELECT '+LTRIM(UserID)+' As UserID,N'''+REPLACE(REPLACE('%'+REPLACE(Uid+'%',',%',''),'%,',''),',',''' As Uid UNION ALL SELECT '+LTRIM(UserID)+' As UserID,''')+''' As Uid' from t1SET @sql='SELECT Uid FROM ('+@sql+') t Where t.UserID='+LTRIM(@UserID)+' Order by Uid'
EXEC (@Sql)
GO
Insert into @t1
Select 1,',60,61,66,',127
Union all Select 2,',60,61,',130
Union all Select 3,',66,67,',128
Union all Select 4,',68,',129
declare @i int
set @i='127'
declare @a varchar(2)
declare @b varchar(2)
declare @c varchar(2)
update @t1 set Uid=RIGHT(uid,len(uid)-CHARINDEX(',',uid) ) where Userid=@i
select @a=left(uid,CHARINDEX(',',uid)-1) from @t1 where Userid=@i
update @t1 set Uid=RIGHT(uid,len(uid)-CHARINDEX(',',uid) ) where Userid=@i
select @b=left(uid,CHARINDEX(',',uid)-1) from @t1 where Userid=@i
update @t1 set Uid=RIGHT(uid,len(uid)-CHARINDEX(',',uid) ) where Userid=@i
select @c=left(uid,CHARINDEX(',',uid)-1) from @t1 where Userid=@i
update @t1 set Uid=RIGHT(uid,len(uid)-CHARINDEX(',',uid) ) where Userid=@i
select @a as uid union select @b union select @c只针对这题...
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #