参考:create table tblTest(PdID int,PdName varchar(100))insert tblTest select 1,'A10' union all select 2,'A20,A20S' union all select 3,'A30,A30K,A30M' union all select 4,'A301' union all select 5,'A301M' select * from tblTest go-- 建立一个辅助的临时表就可以了 SELECT TOP 8000 id = identity(int,1,1) INTO # FROM syscolumns a, syscolumns b -- 这个辅助表是什么含义,为下一步铺垫的目的是什么? -- 以上生成一个临时表
SELECT A.PdID, PdName = SUBSTRING(A.PdName, B.ID, CHARINDEX(',', A.PdName + ',', B.ID) - B.ID) --这个SUBSTRING在B.ID每一次执行的时候的值是多少? FROM tblTest A, # B WHERE SUBSTRING(',' + a.PdName, B.id, 1) = ',' --这个where后面的检索条件又代表什么含义? ORDER BY 1,2 GODROP TABLE tblTest, # 即:CREATE TABLE A(id INT,country VARCHAR(100)) INSERT A SELECT 1,'中国;日本;韩国' UNION ALL SELECT 2,'美国;意大利;法国' UNION ALL SELECT 3,'德国' SELECT * FROM A-- 建立一个辅助的临时表就可以了 SELECT TOP 8000 id = identity(int,1,1) INTO # FROM syscolumns a, syscolumns b SELECT A.ID, COUNTRY = SUBSTRING(A.COUNTRY, B.ID, CHARINDEX(';', A.COUNTRY + ';', B.ID) - B.ID) FROM A, # B WHERE SUBSTRING(';' + a.COUNTRY, B.id, 1) = ';' ORDER BY 1,2 GODROP TABLE A,#id country ----------- ---------------- 1 中国;日本;韩国 2 美国;意大利;法国 3 德国(所影响的行数为 3 行)ID COUNTRY ----------- --------- 1 韩国 1 日本 1 中国 2 法国 2 美国 2 意大利 3 德国(所影响的行数为 7 行)
SQL2005实现: CREATE TABLE A(id INT,country VARCHAR(100)) INSERT A SELECT 1,'中国;日本;韩国' UNION ALL SELECT 2,'美国;意大利;法国' UNION ALL SELECT 3,'德国' --SELECT * FROM ASELECT c.id, B.valueFROM( SELECT id, [country] = CONVERT(xml, '<root><v>' + REPLACE([country], ';', '</v><v>') + '</v></root>') FROM A)cOUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)') FROM c.[country].nodes('/root/v') N(v))B id value ----------- ---------------- 1 日本 1 韩国 2 美国 2 意大利 2 法国 3 德国(7 行受影响)
SQL2000用游标 CREATE TABLE A(id INT,country VARCHAR(100)) INSERT A SELECT 1,'中国;日本;韩国' UNION ALL SELECT 2,'美国;意大利;法国' UNION ALL SELECT 3,'德国' --SELECT * FROM A方法1通过游标实现:declare @tb table(id int, name varchar(50))--用表变量显示效果 DECLARE @id int,@name varchar(50) DECLARE roy CURSOR FOR SELECT * from a OPEN roy FETCH next FROM roy into @id,@name WHILE @@FETCH_STATUS = 0 BEGIN while CHARINDEX(';',@name)>0 begin INSERT @tb select @id,LEFT(@name,CHARINDEX(';',@name)-1) SET @name=STUFF(@name,1,CHARINDEX(';',@name),'') end insert @tb select @id,@name FETCH NEXT FROM roy into @id,@name end CLOSE roy DEALLOCATE roy select * from @tbid name ----------- ----------------------------- 1 中国 1 日本 1 韩国 2 美国 2 意大利 2 法国 3 德国(7 行受影响)
SQL2000方法2用表变量: CREATE TABLE A(id INT,country VARCHAR(100)) INSERT A SELECT 1,'中国;日本;韩国' UNION ALL SELECT 2,'美国;意大利;法国' UNION ALL SELECT 3,'德国' --SELECT * FROM A declare @ta table(id int)--生成1—50递增的表变量 declare @i int,@j int select @i=1,@j=50--定义字符的最大数量 while @i!>@j begin insert @ta select @i select @i=@i+1 end select a.id, 显示列=substring(a.country,b.id,charindex(';',a.country+';',b.id)-b.id) from a,@ta b where substring(';'+a.country,b.id,1)=';' 结果为: id 显示列 ----------- ----------- 1 中国 2 美国 3 德国 1 日本 2 意大利 1 韩国 2 法国(7 行受影响)
我写过一个实现该功能的自定义函数:--将字符串分隔后生成表的形式 CREATE function GetSplitStr ( @SourceSql varchar(8000), --包含分隔符的字符串 @StrSeprate varchar(10) --分隔符 ) returns @temp table(id int, string varchar(100)) as begin set @SourceSql = @SourceSql + @StrSeprate declare @i int, @row int
set @row=1 set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(@StrSeprate,@SourceSql) while (@i > 0) begin insert @temp values(@row, left(@SourceSql,@i-1)) set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) set @row = @row + 1 end if @SourceSql<>'' insert @temp values(@row, @SourceSql) return end 调用方法: select * from dbo.GetSplitStr('小强,小猫,小狗', ',') 可以实现任何一种分隔符的通用。
declare @AllChar varchar(50) declare @FirstChar varchar(50) declare @FirstPoint int declare @lenth int set @AllChar='小明,小强,张小敏' ----可以传入一个字符串 set @lenth=len(@AllChar) create table #Temp_String(FID int identity,Content varchar(50)) set @FirstPoint=charindex(',',@AllChar)while( @FirstPoint>0) begin set @FirstChar=substring(@AllChar,0,@FirstPoint) --select @FirstChar insert into #Temp_String(Content) values (@FirstChar) set @AllChar=substring(@AllChar,@FirstPoint+1,@lenth) set @FirstPoint=charindex(',',@AllChar) end insert into #Temp_String(Content) values (@AllChar) select * from #Temp_String------------ FID CONTENT1 小明 2 小强 3 张小敏
declare @AllChar varchar(50) declare @FirstChar varchar(50) declare @FirstPoint int declare @lenth int set @AllChar='小明,小强,张小敏' ----可以传入一个字符串 set @lenth=len(@AllChar) create table #Temp_String(FID int identity,Content varchar(50)) set @FirstPoint=charindex(',',@AllChar)while( @FirstPoint>0) begin set @FirstChar=substring(@AllChar,0,@FirstPoint) --select @FirstChar insert into #Temp_String(Content) values (@FirstChar) set @AllChar=substring(@AllChar,@FirstPoint+1,@lenth) set @FirstPoint=charindex(',',@AllChar) end insert into #Temp_String(Content) values (@AllChar) select * from #Temp_String------------ FID CONTENT1 小明 2 小强 3 张小敏
select 1,'A10' union all
select 2,'A20,A20S' union all
select 3,'A30,A30K,A30M' union all
select 4,'A301' union all
select 5,'A301M'
select * from tblTest
go-- 建立一个辅助的临时表就可以了
SELECT TOP 8000
id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
-- 这个辅助表是什么含义,为下一步铺垫的目的是什么?
-- 以上生成一个临时表
SELECT
A.PdID,
PdName = SUBSTRING(A.PdName, B.ID, CHARINDEX(',', A.PdName + ',', B.ID) - B.ID) --这个SUBSTRING在B.ID每一次执行的时候的值是多少?
FROM tblTest A, # B
WHERE SUBSTRING(',' + a.PdName, B.id, 1) = ',' --这个where后面的检索条件又代表什么含义?
ORDER BY 1,2
GODROP TABLE tblTest, #
即:CREATE TABLE A(id INT,country VARCHAR(100))
INSERT A
SELECT 1,'中国;日本;韩国' UNION ALL
SELECT 2,'美国;意大利;法国' UNION ALL
SELECT 3,'德国'
SELECT * FROM A-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
A.ID,
COUNTRY = SUBSTRING(A.COUNTRY, B.ID, CHARINDEX(';', A.COUNTRY + ';', B.ID) - B.ID)
FROM A, # B
WHERE SUBSTRING(';' + a.COUNTRY, B.id, 1) = ';'
ORDER BY 1,2
GODROP TABLE A,#id country
----------- ----------------
1 中国;日本;韩国
2 美国;意大利;法国
3 德国(所影响的行数为 3 行)ID COUNTRY
----------- ---------
1 韩国
1 日本
1 中国
2 法国
2 美国
2 意大利
3 德国(所影响的行数为 7 行)
CREATE TABLE A(id INT,country VARCHAR(100))
INSERT A
SELECT 1,'中国;日本;韩国' UNION ALL
SELECT 2,'美国;意大利;法国' UNION ALL
SELECT 3,'德国'
--SELECT * FROM ASELECT c.id, B.valueFROM( SELECT id, [country] = CONVERT(xml, '<root><v>' + REPLACE([country], ';', '</v><v>') + '</v></root>') FROM A)cOUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)') FROM c.[country].nodes('/root/v') N(v))B
id value
----------- ----------------
1 日本
1 韩国
2 美国
2 意大利
2 法国
3 德国(7 行受影响)
CREATE TABLE A(id INT,country VARCHAR(100))
INSERT A
SELECT 1,'中国;日本;韩国' UNION ALL
SELECT 2,'美国;意大利;法国' UNION ALL
SELECT 3,'德国'
--SELECT * FROM A方法1通过游标实现:declare @tb table(id int, name varchar(50))--用表变量显示效果
DECLARE @id int,@name varchar(50)
DECLARE roy CURSOR
FOR SELECT * from a
OPEN roy
FETCH next FROM roy
into @id,@name
WHILE @@FETCH_STATUS = 0
BEGIN
while CHARINDEX(';',@name)>0
begin
INSERT @tb select @id,LEFT(@name,CHARINDEX(';',@name)-1)
SET @name=STUFF(@name,1,CHARINDEX(';',@name),'')
end
insert @tb select @id,@name
FETCH NEXT FROM roy into @id,@name
end
CLOSE roy
DEALLOCATE roy
select * from @tbid name
----------- -----------------------------
1 中国
1 日本
1 韩国
2 美国
2 意大利
2 法国
3 德国(7 行受影响)
CREATE TABLE A(id INT,country VARCHAR(100))
INSERT A
SELECT 1,'中国;日本;韩国' UNION ALL
SELECT 2,'美国;意大利;法国' UNION ALL
SELECT 3,'德国'
--SELECT * FROM A
declare @ta table(id int)--生成1—50递增的表变量
declare @i int,@j int
select @i=1,@j=50--定义字符的最大数量
while @i!>@j
begin
insert @ta select @i
select @i=@i+1
end
select a.id,
显示列=substring(a.country,b.id,charindex(';',a.country+';',b.id)-b.id)
from a,@ta b
where substring(';'+a.country,b.id,1)=';'
结果为:
id 显示列
----------- -----------
1 中国
2 美国
3 德国
1 日本
2 意大利
1 韩国
2 法国(7 行受影响)
CREATE function GetSplitStr
(
@SourceSql varchar(8000), --包含分隔符的字符串
@StrSeprate varchar(10) --分隔符
)
returns @temp table(id int, string varchar(100))
as
begin
set @SourceSql = @SourceSql + @StrSeprate declare
@i int,
@row int
set @row=1
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while (@i > 0)
begin
insert @temp values(@row, left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
set @row = @row + 1
end
if @SourceSql<>''
insert @temp values(@row, @SourceSql)
return
end
调用方法:
select * from dbo.GetSplitStr('小强,小猫,小狗', ',')
可以实现任何一种分隔符的通用。
declare @FirstChar varchar(50)
declare @FirstPoint int
declare @lenth int set @AllChar='小明,小强,张小敏' ----可以传入一个字符串
set @lenth=len(@AllChar)
create table #Temp_String(FID int identity,Content varchar(50))
set @FirstPoint=charindex(',',@AllChar)while( @FirstPoint>0)
begin
set @FirstChar=substring(@AllChar,0,@FirstPoint)
--select @FirstChar
insert into #Temp_String(Content) values (@FirstChar)
set @AllChar=substring(@AllChar,@FirstPoint+1,@lenth)
set @FirstPoint=charindex(',',@AllChar)
end insert into #Temp_String(Content) values (@AllChar)
select * from #Temp_String------------
FID CONTENT1 小明
2 小强
3 张小敏
declare @FirstChar varchar(50)
declare @FirstPoint int
declare @lenth int set @AllChar='小明,小强,张小敏' ----可以传入一个字符串
set @lenth=len(@AllChar)
create table #Temp_String(FID int identity,Content varchar(50))
set @FirstPoint=charindex(',',@AllChar)while( @FirstPoint>0)
begin
set @FirstChar=substring(@AllChar,0,@FirstPoint)
--select @FirstChar
insert into #Temp_String(Content) values (@FirstChar)
set @AllChar=substring(@AllChar,@FirstPoint+1,@lenth)
set @FirstPoint=charindex(',',@AllChar)
end insert into #Temp_String(Content) values (@AllChar)
select * from #Temp_String------------
FID CONTENT1 小明
2 小强
3 张小敏