--轉老大 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 個資料列受到影響)
DECLARE @s1 NVARCHAR(200),@s2 NVARCHAR(200),@s3 NVARCHAR(200) SET @s1='345;456;789' SET @s2='123;345;456;567;678;789'Select @s3=ISNULL(@s3+';','')+ ColFROM (select substring(@s2,b.number,charindex(';',@s2+';',b.number)-b.number) AS COl from master.dbo.spt_values b where charindex(';',';'+@s2,b.number)=b.number AND b.type='P' )T WHERE ';'+@s1+';' NOT LIKE '%;'+COl+';%'SELECT @s3 /* 123;567;678 */
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 個資料列受到影響)
SET @s1='345;456;789'
SET @s2='123;345;456;567;678;789'Select
@s3=ISNULL(@s3+';','')+ ColFROM
(select
substring(@s2,b.number,charindex(';',@s2+';',b.number)-b.number) AS COl
from
master.dbo.spt_values b
where
charindex(';',';'+@s2,b.number)=b.number AND b.type='P'
)T
WHERE ';'+@s1+';' NOT LIKE '%;'+COl+';%'SELECT @s3
/*
123;567;678
*/
请问第二个参数@pos,在我这里也没有用吧。