create table aa
(
id int identity(1,1),
count int,
name varchar(50)
)
我写了一个存储过程 该存储过程接受了一个字符串的参数如: @count varchar(50)
@name varchar(50)@count 的值是 "2,5,6,7,1,4"
@name 的值是 "1,2,3,4,5,6"我怎么截取 @count 为 2 5 6 ....
@name也类似
(
id int identity(1,1),
count int,
name varchar(50)
)
我写了一个存储过程 该存储过程接受了一个字符串的参数如: @count varchar(50)
@name varchar(50)@count 的值是 "2,5,6,7,1,4"
@name 的值是 "1,2,3,4,5,6"我怎么截取 @count 为 2 5 6 ....
@name也类似
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)
while len(@count)>0
begin
insert into tb ([count]) values(left(@count,charindex(',',@count)))
set @count=right(@count,len(@count)-charindex(',',@count))
end
declare @a nvarchar(max)='2,455,6,7,19,4,'
declare @x nvarchar(10);
while LEN(@a)>0
begin
set @x=LEFT(@a,CHARINDEX(',',@a));
select @x;
set @a=STUFF(@a,1,LEN(@x),'');
end
declare @ssql varchar(500)
declare @endsql varchar(8000)
set @ssql = '4,5,6,7'
set @endsql = ''
set @endsql = 'select ''' +replace(@ssql,',',''' union all select ''')+''''
exec(@endsql)
看看吧