22,209
社区成员
发帖
与我相关
我的任务
分享
drop function F_strSpit12
go
----->自定义函数
create function F_strSpit12(@s varchar(200))
returns @t table(col varchar(15))
as
begin
insert into @t select substring(@s,number,1)
from MASTER..spt_values where number <=len(@s)
while not exists(select 1 from @t where col = @s)
insert @t
SELECT T3.COL + T2.COL FROM @t AS T2,@t AS T3 WHERE len(t3.col) = 1 and charindex(T3.COL,T2.COL) =0 and T2.COL > T3.COL
return
end
go
----->计算
if object_id('tempdb..#result') is not null drop table #result
go
create table #result
([ID] [int] IDENTITY(1001,1) NOT NULL,col varchar(15), CON int)
declare @s varchar(200)
set @s='ABC' ----无法把'ABC'替代为'D001 D002 D003'
;with t as(
select distinct col,len(col) 'CON'
from dbo.F_strSpit12(@s)
where len(col) > 0)
insert into #result(col,CON)
select col,CON
from t
order by CON,col
select * from #result
*/
id col CON
-----------------------------------
1001 A 1
1002 B 1
1003 C 1
1004 AB 2
1005 AC 2
1006 BC 2
1007 ABC 3
*/
*/
id col CON
-----------------------------------
1001 D001 1
1002 D002 1
1003 D003 1
1004 D001 D002 2
1005 D001 D003 2
1006 D002 D003 2
1007 D001 D002 D003 3
*/
create function F_strSpit12(
@s varchar(200))
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)returns @t table(col varchar(15))
as
begin
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+' ')-3
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,3,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-3),''))
insert into @t
select substring(@s,number,1)
from MASTER..spt_values where number <=len(@s)
while not exists(select 1 from @t where col = @s)
insert @t
SELECT T3.COL + T2.COL FROM @t AS T2,@t AS T3 WHERE len(t3.col) = 1 and charindex(T3.COL,T2.COL) =0 and T2.COL > T3.COL
return
end
go
----->计算
if object_id('tempdb..#result') is not null drop table #result
go
create table #result
([ID] [int] IDENTITY(1001,1) NOT NULL,col varchar(15), CON int)
declare @s varchar(200)
set @s='ABC' ----无法把'ABC'替代为'D001 D002 D003'
;with t as(
select distinct col,len(col) 'CON'
from dbo.F_strSpit12(@s)
where len(col) > 0)
insert into #result(col,CON)
select col,CON
from t
order by CON,col
select * from #result
不知问题出在哪儿?盼解,谢谢--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000), --包含多个数据项的字符串
@pos int, --要获取的数据项的位置
@split varchar(10) --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
IF @s IS NULL RETURN(NULL)
DECLARE @splitlen int
SELECT @splitlen=LEN(@split+'a')-2
WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
SELECT @pos=@pos-1,
@s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GO
所以请教大神,盼解,谢谢!
create function dbo.F_strSpit12(@s varchar(200))
returns @t table(ID int identity(1001,1) not null,
col varchar(15),
CON int)
as
begin
declare @valuelist table(id int identity(1,1) not null, val varchar(50))
insert into @valuelist(val)
select substring(a.s,b.number,charindex(' ',a.s+' ',b.number)-b.number) s
from (select @s 's') a
inner join master.dbo.spt_values b on b.number between 1 and len(a.s)
and substring(' '+a.s,b.number,1)=' '
where b.type=N'P'
insert into @t(col,CON)
select val,1
from @valuelist
while not exists(select 1 from @t where col=@s)
begin
insert @t(col,CON)
select b.col+' '+a.COL,a.CON+1
from @t a,@t b
where b.CON=1 and charindex(b.col,a.col,1)=0 and a.col>b.col
and not exists(select 1 from @t c where c.col=b.col+' '+a.COL)
end
return
end
declare @s varchar(200)
set @s='D001 D002 D003'
select * from dbo.F_strSpit12(@s)
/*
ID col CON
----------- --------------------- -----------
1001 D001 1
1002 D002 1
1003 D003 1
1004 D001 D002 2
1005 D001 D003 2
1006 D002 D003 2
1007 D001 D002 D003 3
(7 row(s) affected)
*/