给您个例子
create table tb1(c1 int , c2 varchar(50))
insert into tb1 select 1, '/a/b/c/d.txt'
UNION ALL SELECT 2, '/a/b/cc/d.txt'
UNION ALL SELECT 3, '/a/bb/c/d.txt'
UNION ALL SELECT 4, '/a/bb/cc/d.html'
UNION ALL SELECT 5, '/aa/bb/d.tt'
UNION ALL SELECT 6, '/aa/bb/dd.tt'
go/*
名称:fn_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
go--调用
declare @str as varchar(30)
set @str = '/a/b/c,/a/bb,/aa/bb/d.tt'select * from tb1 m,
(select * from dbo.fn_split(@str,',')) n
where m.c2 + '/' like '%' + n.a + '/%'drop table tb
drop function dbo.fn_split /*
c1 c2 a
----------- -------------------------------------------------- -------------
1 /a/b/c/d.txt /a/b/c
3 /a/bb/c/d.txt /a/bb
4 /a/bb/cc/d.html /a/bb
5 /aa/bb/d.tt /aa/bb/d.tt(所影响的行数为 4 行)*/
create table tb1(c1 int , c2 varchar(50))
insert into tb1 select 1, '/a/b/c/d.txt'
UNION ALL SELECT 2, '/a/b/cc/d.txt'
UNION ALL SELECT 3, '/a/bb/c/d.txt'
UNION ALL SELECT 4, '/a/bb/cc/d.html'
UNION ALL SELECT 5, '/aa/bb/d.tt'
UNION ALL SELECT 6, '/aa/bb/dd.tt'
go/*
名称:fn_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
go--调用
declare @str as varchar(30)
set @str = '/a/b/c,/a/bb,/aa/bb/d.tt'select * from tb1 m,
(select * from dbo.fn_split(@str,',')) n
where m.c2 + '/' like '%' + n.a + '/%'drop table tb
drop function dbo.fn_split /*
c1 c2 a
----------- -------------------------------------------------- -------------
1 /a/b/c/d.txt /a/b/c
3 /a/bb/c/d.txt /a/bb
4 /a/bb/cc/d.html /a/bb
5 /aa/bb/d.tt /aa/bb/d.tt(所影响的行数为 4 行)*/
改编:爱新觉罗.毓华 2007-12-16 广东深圳有表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 #2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc(5 行受影响)
*/
insert @t select 'a','1,2,3,4'
insert @t select 'b','6,7,8,9'
insert @t select 'b','20,23,24'
insert @t select 'a','30,31,32'select a.name, substring(a.arrid, b.number, charindex(',', a.arrid + ',', b.number) - b.number)
from @t a, master..spt_values B
where type='p' and substring(',' + a.arrid, b.number, 1) = ','
order by a.name
/*
name arrid
---------- --------------------
a 1
a 2
a 3
a 4
a 30
a 31
a 32
b 6
b 7
b 8
b 9
b 20
b 23
b 24(14 行受影响)
*/
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
http://topic.csdn.net/u/20090209/08/a945701c-e0d5-40cb-85f2-f4f56ac2999b.html
returns @R table (Col nvarchar(100))
as
begin
declare @StrLen int
set @StrLen=len(@Str)
while charindex(@Split,@StrLen)>0
begin
insert into @R values(left(@Str,charindex(@Split,@StrLen)-1))
set @Str=stuff(@Str,1,charindex(@Split,@StrLen),'')
end
insert into @R values(@Str)
return
end declare @a nvarchar(4000)
set @a='1,23,a'
select dbo.FC_SlpitStr(@a,',')
--(2)动态T-sql语句
declare @S varchar(100)
set @s='1,23,a'declare @sql varchar(100)
set @sql='select col='''+replace(@S, ',' , ''' union all select ''')+''''
print(@sql)
分拆列值 原著:邹建
改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-12-16 广东深圳 有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc 1. 旧的解决方法(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 # 2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLACE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)B DROP TABLE tb /*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc (5 行受影响)
*/
(
Name varchar(20) ,
Arrid varchar(40)
)
declare cur cursor for select Name, arrid from 表名 order by name
declare @cName varchar(20)
declare @cArrid varchar(40)
declare @cSplit varchar(1)
declare @cMid varchar(40)
declare @cLeft varchar(40)
declare @nPOS int
set @cSplit = ','
set @nPOS = 0
open cur
fetch next from cur into @cName, @cArrid
while( @@fetch_status = 0 )
begin
set @cMid = @cArrid
while( @cLeft <> '' )
begin
set @nPOS = charindex(@cSplit, @cMid)
set @cLeft = @cMid
if( @nPOS > 0 )
begin
set @cLeft = left( @cMid, @nPOS - 1 )
set @cMid = substring( @cMid, @nPOS, len(@cMid) )
end
insert into @tRet( Name, Arrid ) values( @cName, @cLeft )
end
fetch next from cur into @cName, @cArrid
endclose cur
deallocate cur
select * from @tRet
name arrid
a 1,2,3,4
b 6,7,8,9
b 20,23,24
a 30,31,32
经过函数转换之后的表
name arrid
a 1
a 2
a 3
a 4
a 30
a 31
a 32
b 6
b 7
b 8
b 9
b 20
b 23
b 24具体函数如下
/*这里的tt表对应的是要转换数据的那个表,要转换的字段的分隔符用的是逗号(,)*/
create function transform_content()
return @trfered_tab table(name varchar(10),arrid varchar(100))
as
begin
--initialize table
declare @rows_added int
--cannot access the temporary table in the function,so modfiy the below code
--select id=identity(int,1,1),a.name,a.arrid into #tmp_tab1 from tt a
declare @tmp_tab table(id int identity(1,1),name varchar(10),arrid varchar(100))
insert into @tmp_tab
select name,arrid from tt --这里是要转换的表,对应改成你数据库中对应的表名
declare @meida_tab table(name varchar(10),arrid varchar(100))
set @rows_added=@@rowcount
while @rows_added>0
begin
--read out the value of arrid
declare @tmp_nm varchar(10)
declare @tmp_str varchar(100)
set @tmp_nm=(select nm from @tmp_tab where id=@rows_added)
set @tmp_str=(select arrid from @tmp_tab where id=@rows_added)
set @tmp_str=@tmp_str+','
--handle the field of arrid,split the string on the certain splitor
declare @sub_str varchar(100)
declare @str_len int
set @str_len=len(@tmp_str)
while @str_len>0
begin
--if your splitor is not ',',please replace the corresponding position with your splitor
set @sub_str=substring(@tmp_str,1,charindex(',',@tmp_str,1)-1)
insert into @meida_tab values(@tmp_nm,@sub_str)
set @tmp_str=substring(@tmp_str,charindex(',',@tmp_str,1)+1,len(@tmp_str)-charindex(',',@tmp_str,1))
set @str_len=len(@tmp_str)
end
set @rows_added=@rows_add-1
end
insert into @trfered_tab
select *
from @meida_tab
order by name,cast(arrid as int)
return
end
jzt_designer 写的我运行了函数写的不完整,请帮忙再写完整点,出现了以下错误
服务器: 消息 156,级别 15,状态 1,过程 transform_content,行 2
在关键字 'return' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,过程 transform_content,行 2
必须声明变量 '@trfered_tab'。
服务器: 消息 137,级别 15,状态 1,过程 transform_content,行 36
必须声明变量 '@rows_add'。
服务器: 消息 137,级别 15,状态 1,过程 transform_content,行 40
必须声明变量 '@trfered_tab'。
DECLARE @tab TABLE(
NAME VARCHAR(2), arrid VARCHAR(20))
INSERT INTO @tab
SELECT 'a', '1,2,3,4' UNION ALL
SELECT 'b', '6,7,8,9' UNION ALL
SELECT 'b', '20,23,24' UNION ALL
SELECT 'a', '30,31,32'
DROP TABLE #
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b -->测试SQL
SELECT A.NAME, SUBSTRING(A.[arrid], B.id, CHARINDEX(',', A.[arrid] + ',', B.id) - B.id)
FROM @tab A, # B
WHERE SUBSTRING(',' + A.[arrid], B.id, 1) = ','-->结果
/*
NAME
---- --------------------
a 1
a 2
a 3
a 4
b 6
b 7
b 8
b 9
b 20
b 23
b 24
a 30
a 31
a 32(14 行受影响)*/
return @trfered_tab table(name varchar(10),arrid varchar(100))
改成returns这里写错了,再试试
create function transform_content()
returns @trfered_tab table(name varchar(10),arrid varchar(100))
as
begin
--initialize table
declare @rows_added int
--cannot access the temporary table in the function,so modfiy the below code
--select id=identity(int,1,1),a.name,a.arrid into #tmp_tab1 from tt a
declare @tmp_tab table(id int identity(1,1),name varchar(10),arrid varchar(100))
insert into @tmp_tab
select name,arrid from tt --这里是要转换的表,对应改成你数据库中对应的表名 declare @meida_tab table(name varchar(10),arrid varchar(100))
set @rows_added=@@rowcount
while @rows_added>0
begin
--read out the value of arrid
declare @tmp_nm varchar(10)
declare @tmp_str varchar(100)
set @tmp_nm=(select nm from @tmp_tab where id=@rows_added)
set @tmp_str=(select arrid from @tmp_tab where id=@rows_added)
set @tmp_str=@tmp_str+','
--handle the field of arrid,split the string on the certain splitor
declare @sub_str varchar(100)
declare @str_len int
set @str_len=len(@tmp_str)
while @str_len>0
begin
--if your splitor is not ',',please replace the corresponding position with your splitor
set @sub_str=substring(@tmp_str,1,charindex(',',@tmp_str,1)-1)
insert into @meida_tab values(@tmp_nm,@sub_str)
set @tmp_str=substring(@tmp_str,charindex(',',@tmp_str,1)+1,len(@tmp_str)-charindex(',',@tmp_str,1))
set @str_len=len(@tmp_str)
end
set @rows_added=@rows_added-1
end insert into @trfered_tab
select *
from @meida_tab
order by name,cast(arrid as int) return
end