--> 数据库版本: --> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 --> 测试数据:[TB] IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') AND type in (N'U')) DROP TABLE [TB] GO---->建表 create table [TB]([id] varchar(5)) insert [TB] select '1,2,3' union all select '4,5,6' GO --2005以上新方法: SELECT b.col FROM (SELECT col=CAST('<v>'+REPLACE(id,',','</v><v>')+'</v>' AS xml) FROM [TB]) a OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') FROM a.col.nodes('/v') AS T(C)) b --> 删除表格 --DROP TABLE [TB]不用charindex ? 那用啥?
if not object_id('tb') is null drop table tb Go Create table tb([title] nvarchar(1),[content] nvarchar(7)) Insert tb select N'A',N'J|K|M|Y' union all select N'B',N'N|P|Y|Z' union all select N'C',N'G' union all select N'D',N'Q|Y' Go SELECT a.[title], [content]=SUBSTRING(a.[content],number,CHARINDEX('|',a.[content]+'|',number)-b.number) FROM tb a JOIN master..spt_values b ON b.type='p' AND CHARINDEX('|','|'+a.[content],number)=number /* title content ----- ------- A J A K A M A Y B N B P B Y B Z C G D Q D Y(11 row(s) affected) */
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([id] varchar(5))
insert [TB]
select '1,2,3' union all
select '4,5,6'
GO
--2005以上新方法:
SELECT b.col
FROM (SELECT col=CAST('<v>'+REPLACE(id,',','</v><v>')+'</v>' AS xml) FROM [TB]) a
OUTER APPLY (SELECT col=T.C.value('.','varchar(50)') FROM a.col.nodes('/v') AS T(C)) b
--> 删除表格
--DROP TABLE [TB]不用charindex ? 那用啥?
drop table tb
Go
Create table tb([title] nvarchar(1),[content] nvarchar(7))
Insert tb
select N'A',N'J|K|M|Y' union all
select N'B',N'N|P|Y|Z' union all
select N'C',N'G' union all
select N'D',N'Q|Y'
Go
SELECT
a.[title],
[content]=SUBSTRING(a.[content],number,CHARINDEX('|',a.[content]+'|',number)-b.number)
FROM tb a
JOIN master..spt_values b
ON b.type='p'
AND CHARINDEX('|','|'+a.[content],number)=number
/*
title content
----- -------
A J
A K
A M
A Y
B N
B P
B Y
B Z
C G
D Q
D Y(11 row(s) affected)
*/