找一個給你 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_GetStr] GO--分段截取函数 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 GODECLARE @s NVARCHAR(1000) SET @s='wer,2sf,234,sdfs,,sfd,sdf,dsfsd,,,Test,,sdf' SELECT dbo.[f_GetStr](@s,11,',')
--先拆分,查找后再合并 if object_id('tb') is not null drop table tb go create table tb ( a varchar(100) ) go insert into tb select 'wer,2sf,234,sdfs,,sfd,sdf,dsfsd,,,Test,,sdf' go with cte as ( select * from( select A,number,sub=substring(a,number,charindex(',',a+',',number)-number),row=row_number() over(order by getdate()) from tb a cross join master..spt_values b where type='p' and number between 1 and len(a) and substring(','+a,number,1)=',' )t1 where row>10 ) select stuff((select ','+sub from cte for xml path('')),1,1,'') /* ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Test,,sdf(1 行受影响)*/
--轉老大 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_GetStr] GO --分段截取函数 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 GOselect dbo.f_GetStr('2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00 ',7,'∮') /* ---------------------------------------------------------------------------------------------------- 786(1 個資料列受到影響)
TO geniuswjt:我们有个考勤软件,每天的排班是按照这个来记录的,他的思路是把每一年的排班每天用逗号隔开来及记录相应的班次号!我有些报表需要重新读取当天的班次所以只好重新找当天的班次记录。TO 老大: 非常感谢你的帮忙,很受教!再次感谢大家!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_GetStr] GO--分段截取函数 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 GODECLARE @s NVARCHAR(1000) SET @s='wer,2sf,234,sdfs,,sfd,sdf,dsfsd,,,Test,,sdf' SELECT dbo.[f_GetStr](@s,11,',')
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO--分段截取函数
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
GODECLARE @s NVARCHAR(1000)
SET @s='wer,2sf,234,sdfs,,sfd,sdf,dsfsd,,,Test,,sdf'
SELECT dbo.[f_GetStr](@s,11,',')
--先拆分,查找后再合并
if object_id('tb') is not null
drop table tb
go
create table tb
(
a varchar(100)
)
go
insert into tb select 'wer,2sf,234,sdfs,,sfd,sdf,dsfsd,,,Test,,sdf'
go
with cte as
(
select * from(
select A,number,sub=substring(a,number,charindex(',',a+',',number)-number),row=row_number() over(order by getdate()) from tb a cross join master..spt_values b where type='p' and number between 1 and len(a) and substring(','+a,number,1)=','
)t1 where row>10
)
select stuff((select ','+sub from cte for xml path('')),1,1,'')
/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Test,,sdf(1 行受影响)*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO
--分段截取函数
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
GOselect dbo.f_GetStr('2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00 ',7,'∮')
/*
----------------------------------------------------------------------------------------------------
786(1 個資料列受到影響)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO--分段截取函数
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
GODECLARE @s NVARCHAR(1000)
SET @s='wer,2sf,234,sdfs,,sfd,sdf,dsfsd,,,Test,,sdf'
SELECT dbo.[f_GetStr](@s,11,',')