declare @sql varchar(100) set @sql='1003*1063,1012*1199,1012*1214,1012*1205'+',' while charindex(',',@sql)>0 begin print substring(@sql,charindex('*',@sql)+1,charindex(',',@sql)-charindex('*',@sql)-1) set @sql=substring(@sql,charindex(',',@sql)+1,len(@sql)-charindex(',',@sql)) end
charindex substring 就行啦
--轉老大 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_SetStr]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_SetStr] GO --分段更新函数 CREATE FUNCTION dbo.f_SetStr( @s varchar(8000), --包含数据项的字符串 @pos int, --要更新的数据项的段 @value varchar(100), --更新后的值 @split varchar(10) --数据分隔符 )RETURNS varchar(8000) AS BEGIN DECLARE @splitlen int,@p1 int,@p2 int SELECT @splitlen=LEN(@split+'a')-2, @p1=1, @p2=CHARINDEX(@split,@s+@split) WHILE @pos>1 AND @p1<=@p2 SELECT @pos=@pos-1, @p1=@p2+@splitlen+1, @p2=CHARINDEX(@split,@s+@split,@p1) RETURN(CASE WHEN @p1<@p2 THEN STUFF(@s,@p1,@p2-@p1,@value) WHEN @p2>LEN(@s) THEN @s+@value WHEN @p2=@p1 THEN STUFF(@s,@p1,0,@value) ELSE @s END) END GO select dbo.f_setstr('0,0,0,0,0,0,0,0',3,1,',') /* -------------------------- 0,0,1,0,0,0,0,0(1 個資料列受到影響) */declare @tb table([ID] int,[STR] varchar(45)) insert @tb select 1,'2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00' SELECT [STR] FROM ( SELECT a.id, [STR]=SUBSTRING(a.[STR],number,CHARINDEX('∮',a.[STR]+'∮',number)-b.number),ROW_NUMBER() OVER (ORDER BY GETDATE()) AS RID FROM @tb a JOIN master..spt_values b ON b.type='p' AND CHARINDEX('∮','∮'+a.[STR],number)=number ) A WHERE A.RID=7/* STR --------------------------------------------- 786(1 row(s) affected)
/* 功能:实现split功能的函数 */create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns @temp table (a varchar(20)) as begin declare @i intset @inputstr = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator, @inputstr)while @i >= 1 begin insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) set @i = charindex(@seprator, @inputstr) endif @inputstr <> '\' insert @temp values(@inputstr)return end go--调用declare @s varchar(1000)set @s='1003*1063,1012*1199,1012*1214,1012*1205'select substring(a ,charindex('*',a) + 1 , len(a)) from dbo.fn_split(@s,',')drop function dbo.fn_split /*
set @sql='1003*1063,1012*1199,1012*1214,1012*1205'+','
while charindex(',',@sql)>0
begin
print substring(@sql,charindex('*',@sql)+1,charindex(',',@sql)-charindex('*',@sql)-1)
set @sql=substring(@sql,charindex(',',@sql)+1,len(@sql)-charindex(',',@sql))
end
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_SetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_SetStr]
GO
--分段更新函数
CREATE FUNCTION dbo.f_SetStr(
@s varchar(8000), --包含数据项的字符串
@pos int, --要更新的数据项的段
@value varchar(100), --更新后的值
@split varchar(10) --数据分隔符
)RETURNS varchar(8000)
AS
BEGIN
DECLARE @splitlen int,@p1 int,@p2 int
SELECT @splitlen=LEN(@split+'a')-2,
@p1=1,
@p2=CHARINDEX(@split,@s+@split)
WHILE @pos>1 AND @p1<=@p2
SELECT @pos=@pos-1,
@p1=@p2+@splitlen+1,
@p2=CHARINDEX(@split,@s+@split,@p1)
RETURN(CASE
WHEN @p1<@p2 THEN STUFF(@s,@p1,@p2-@p1,@value)
WHEN @p2>LEN(@s) THEN @s+@value
WHEN @p2=@p1 THEN STUFF(@s,@p1,0,@value)
ELSE @s END)
END
GO
select dbo.f_setstr('0,0,0,0,0,0,0,0',3,1,',')
/*
--------------------------
0,0,1,0,0,0,0,0(1 個資料列受到影響)
*/declare @tb table([ID] int,[STR] varchar(45))
insert @tb
select 1,'2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00'
SELECT [STR]
FROM (
SELECT a.id,
[STR]=SUBSTRING(a.[STR],number,CHARINDEX('∮',a.[STR]+'∮',number)-b.number),ROW_NUMBER() OVER (ORDER BY GETDATE()) AS RID
FROM @tb a
JOIN master..spt_values b
ON b.type='p' AND CHARINDEX('∮','∮'+a.[STR],number)=number
) A
WHERE A.RID=7/*
STR
---------------------------------------------
786(1 row(s) affected)
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(20))
as begin
declare @i intset @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @s varchar(1000)set @s='1003*1063,1012*1199,1012*1214,1012*1205'select substring(a ,charindex('*',a) + 1 , len(a)) from dbo.fn_split(@s,',')drop function dbo.fn_split /*
--------------------
1063
1199
1214
1205(所影响的行数为 4 行)
*/