[code=SQL]create function f_split(@full varchar(8000),@split varchar(10)) returns @re table (col2 varchar(100))
as
begin
declare @splitlen int
set @splitlen=len(@split+'a')-2
while charindex(@split,@full)>0
begin
insert @re values(left(@full,charindex(@split,@full)-1))
set @full=stuff(@full,1,charindex(@split,@full)+@splitlen,'')
end
insert @re values(@full)
return
endcode]
上面是函数,是一个吧列中的值按照特定字符分割的函数,返回值那里有点搞不清楚,还有如何调用她啊!
as
begin
declare @splitlen int
set @splitlen=len(@split+'a')-2
while charindex(@split,@full)>0
begin
insert @re values(left(@full,charindex(@split,@full)-1))
set @full=stuff(@full,1,charindex(@split,@full)+@splitlen,'')
end
insert @re values(@full)
return
endcode]
上面是函数,是一个吧列中的值按照特定字符分割的函数,返回值那里有点搞不清楚,还有如何调用她啊!
select * from dbo.f_split('a,b,c',',')
一个表
create table one
(
col1 int ,
col2 varchar(50)
)
插入记录
insert into one values(3,'a,b,c,e,f')
insert into one values(4,'dt,st,ss,d')
如何变成
col1 col2
1 a
2 b
3 c
4 e
5 f
6 dt
.....
select * from dbo.f_split('1,2,3',',')
create table one
(
col1 int identity(1,1) ,
col2 varchar(50)
) insert into one (col2)
select * from dbo.f_split('a,b,c',',') union all
select * from dbo.f_split('dt,st,ss,d',',')
create table one
(
col1 int identity(1,1) ,
col2 varchar(50)
) insert into one (col2)
select * from dbo.f_split('a,b,c',',') union all
select * from dbo.f_split('dt,st,ss,d',',')select * from one
/*
col1 col2
----------- --------------------------------------------------
1 a
2 b
3 c
4 d
5 dt
6 ss
7 st
*/
(
@full varchar(8000),
@split varchar(10)
)
returns @re table (col2 varchar(100))
as
begin
declare @splitlen int
set @splitlen=len(@split+'a')-2
while charindex(@split,@full)>0
begin
insert @re values(left(@full,charindex(@split,@full)-1))
set @full=stuff(@full,1,charindex(@split,@full)+@splitlen,'')
end
insert @re values(@full)
return
endselect * from dbo.f_split('11,22,33',',')
go
create table tb (id int,value varchar(30))
insert into tb
select 1,'a,b,c' union all
select 2,'d,e,f' union all
select 3,'m,n,bb,mm'
select * from tb
/*
id value
----------- ------------------------------
1 a,b,c
2 d,e,f
3 m,n,bb,mm
*/
go create table one
(
col1 int identity(1,1) ,
col2 varchar(50)
) insert into one (col2)
select 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 select * from one/*
col1 col2
----------- --------------------------------------------------
1 a
2 b
3 c
4 d
5 e
6 f
7 m
8 n
9 bb
10 mm
*/
--创建一个表
create table tb (id int,value varchar(30))
insert into tb
select 1,'a,b,c' union all
select 2,'d,e,f' union all
select 3,'m,n,bb,mm'--取出数据
select * from tb--按,分割后取出数据
select 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 a,b,c
2 d,e,f
3 m,n,bb,mm(3 row(s) affected)value
----------------------------------------------------------------------------------------------------
a
b
c
d
e
f
m
n
bb
mm (10 row(s) affected)
*/
select 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 执行以上代码的时候提示错误
服务器: 消息 156,级别 15,状态 1,行 6
在关键字 'outer' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 7
第 7 行: '(' 附近有语法错误。
是 怎么回事啊,