日期:2014-05-17 浏览次数:20549 次
create table #ta(ID int,部位 varchar(50))
insert into #ta
select '10001','头部,腹部'
with cte as
(
select ID,部位+',' 部位
from #ta
),cte1 as
(
select ID,left(部位,CHARINDEX(',',部位)-1) as a,
right(部位, len(部位)-CHARINDEX(',',部位))b
from cte
union all
select ID,left(b,CHARINDEX(',',b)-1) as a,
right(b,len(b)-CHARINDEX(',',b))b
from cte1
where len(b)>1
)
select ID,a from cte1
------------------------------------------------
ID a
----------- ---------------------------------------------------
10001 头部
10001 腹部
(2 行受影响)
CREATE function [dbo].[f_split](@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
/*--实现split功能的函数*/
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
GO
WITH cte AS
(
SELECT 10001 AS&n