/* 功能:实现split功能的函数 */create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns @temp table (a varchar(200)) as begin declare @i int set @inputstr = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator, @inputstr) while @i >= 1 begin insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) set @i = charindex(@seprator, @inputstr) end if @inputstr <> '\' insert @temp values(@inputstr) return end gocreate table #t(科目 varchar(100),学生 varchar(100))insert into #t select '语文','张三;李四' --select * from #t create table #tt(科目 varchar(100),学生 varchar(100))declare cur cursor read_only for select 科目,学生 from #tdeclare @km varchar(40),@xs varchar(100) open curfetch next from cur into @km,@xs while (@@fetch_status=0) begin insert into #tt select @km,a from dbo.fn_split(@xs,';') fetch next from cur into @km,@xs endclose cur deallocate cur go select * from #tt drop table #t drop table #tt go drop function fn_split go
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 行)
if object_id('pubs..A') is not null drop table A gocreate table A(科目 varchar(10),学生 varchar(10)) insert into A(科目,学生) values('语文', '张三;李四') insert into A(科目,学生) values('数学', '张三;李四')-- 建立一个辅助的临时表就可以了 SELECT TOP 8000 科目 = identity(int,1,1) INTO # FROM syscolumns a, syscolumns b SELECT A.科目, 学生 = SUBSTRING(A.学生, B.科目, CHARINDEX(';', A.学生 + ';', B.科目) - B.科目) FROM A, # B WHERE SUBSTRING(';' + a.学生, B.科目, 1) = ';' ORDER BY 1,2 GODROP TABLE A,#/* 科目 学生 ---------- ---------- 数学 李四 数学 张三 语文 李四 语文 张三(所影响的行数为 4 行) */
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i int set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr) while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end if @inputstr <> '\'
insert @temp values(@inputstr) return
end
gocreate table #t(科目 varchar(100),学生 varchar(100))insert into #t select '语文','张三;李四'
--select * from #t
create table #tt(科目 varchar(100),学生 varchar(100))declare cur cursor
read_only
for select 科目,学生 from #tdeclare @km varchar(40),@xs varchar(100)
open curfetch next from cur into @km,@xs
while (@@fetch_status=0)
begin
insert into #tt
select @km,a
from dbo.fn_split(@xs,';')
fetch next from cur into @km,@xs
endclose cur
deallocate cur
go
select * from #tt
drop table #t
drop table #tt
go
drop function fn_split
go
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 行)
drop table A
gocreate table A(科目 varchar(10),学生 varchar(10))
insert into A(科目,学生) values('语文', '张三;李四')
insert into A(科目,学生) values('数学', '张三;李四')-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 科目 = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
A.科目,
学生 = SUBSTRING(A.学生, B.科目, CHARINDEX(';', A.学生 + ';', B.科目) - B.科目)
FROM A, # B
WHERE SUBSTRING(';' + a.学生, B.科目, 1) = ';'
ORDER BY 1,2
GODROP TABLE A,#/*
科目 学生
---------- ----------
数学 李四
数学 张三
语文 李四
语文 张三(所影响的行数为 4 行)
*/