declare @s nvarchar(max) set @s='10,20,30,40,50,60,70,80,90,100,150,' --1.直接获取 select num=substring(@s,charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1,CHARINDEX(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1)-(charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1)) go --2.自定义函数获取 if OBJECT_ID('fn_str') is not null drop function fn_str go create function fn_str( @s nvarchar(max),--输入字符 @flag nvarchar(10),--分隔符 @n int--取第n个分隔符与第n-1个分隔符前的字符 ) returns varchar(20) as begin declare @i int,@r nvarchar(100) set @i=0 while @i<@n begin set @r=left(@s,charindex(@flag,@s)-1) set @s=stuff(@s,1,charindex(@flag,@s),'') set @i=@i+1 end return @r end go--调用函数: declare @s nvarchar(max) set @s='10,20,30,40,50,60,70,80,90,100,150,' select dbo.fn_str(@s,',',5) as num/* num 50 */
我相信这个可以解决你的问题create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(100)) returns @temp table(F1 varchar(100), id int identity(1,1) ) as begin declare @ch as varchar(100) set @SourceSql=@SourceSql+@StrSeprate while(@SourceSql<>'') begin set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1) insert @temp values(@ch) set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'') end return end select * from f_splitstr('10,20,30,40,50,60,70,80,90,100,150,',',')
create function char_index(@string varchar(8000),@char varchar(10),@index smallint) --@string:待查找字符串,@index:查找位置 returns smallint as begin declare @i tinyint,--当前找到第@i个 @position tinyint--所在位置 set @position=@index; set @i=0; while charindex(@char,@string,@position)>0 begin set @position=charindex(@char,@string,@position)+1; set @i=@i+1; if @i=@index begin return @position-1; end end return 0;--0表示未找到 end
没注意看是修改,代码如下:declare @s nvarchar(max) set @s='10,20,30,40,50,60,70,80,90,100,150,' --假如将第五个逗号前的50修改为55 --1.直接修改 set @s=STUFF(@s,charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1,CHARINDEX(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1)-(charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1),'55') select @s /* 10,20,30,40,55,60,70,80,90,100,150, */ go --2.存储过程修改 if OBJECT_ID('sp_upstr') is not null drop proc sp_upstr go create proc sp_upstr( @s nvarchar(max),--输入字符 @flag nvarchar(10),--分隔符 @n int,--取第n个分隔符与第n-1个分隔符前的字符 @r nvarchar(50) --替换值 ) as begin declare @s1 nvarchar(max),@i int,@j int,@k int select @s1=@flag+@s,@i=0,@j=0,@k=0 while @i<@n begin set @k=@j set @j=@j+LEN(left(@s1,CHARINDEX(@flag,@s1))) set @s1=stuff(@s1,1,charindex(@flag,@s1),'') set @i=@i+1 end if @n=1 set @s=STUFF(@s,@j,CHARINDEX(@flag,@s)-1,@r) else set @s=STUFF(@s,@j,@j-@k-1,@r) select @s end go --调用过程 declare @s nvarchar(max) set @s='10,20,30,40,50,60,70,80,90,100,150,' exec sp_upstr @s,',',5,'55' /* 10,20,30,40,55,60,70,80,90,100,150, */
Create table #tt(tid int identity(1,1) primary key,tn varchar(1)) insert into #tt select '1' union Select '2' union Select '3' union Select '4' union Select '5' union Select '6'
declare @s varchar(100),@i int,@index intSelect @s= '10,20,30,40,50,60,70,80,90,100,150,', @i=1,@index=0Update t set @index=charindex(',',@s),@s=stuff(@s,charindex(',',@s),1, case @i when 4 then '~$!' else '~$' end ), @i=@i+1 from #tt t where t.tid<=5 and @i<=5Select Replace(Stuff(@s,charindex('~$!',@s),@index-charindex('~$!',@s),',999'),'~$',',') Drop table #tt --我也来一个呵呵
set @s='10,20,30,40,50,60,70,80,90,100,150,'
--1.直接获取
select num=substring(@s,charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1,CHARINDEX(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1)-(charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1))
go
--2.自定义函数获取
if OBJECT_ID('fn_str') is not null
drop function fn_str
go
create function fn_str(
@s nvarchar(max),--输入字符
@flag nvarchar(10),--分隔符
@n int--取第n个分隔符与第n-1个分隔符前的字符
)
returns varchar(20)
as
begin
declare @i int,@r nvarchar(100)
set @i=0
while @i<@n
begin
set @r=left(@s,charindex(@flag,@s)-1)
set @s=stuff(@s,1,charindex(@flag,@s),'')
set @i=@i+1
end
return @r
end
go--调用函数:
declare @s nvarchar(max)
set @s='10,20,30,40,50,60,70,80,90,100,150,'
select dbo.fn_str(@s,',',5) as num/*
num
50
*/
returns @temp table(F1 varchar(100),
id int identity(1,1)
)
as
begin
declare @ch as varchar(100)
set @SourceSql=@SourceSql+@StrSeprate
while(@SourceSql<>'')
begin
set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1)
insert @temp values(@ch)
set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')
end
return
end select * from f_splitstr('10,20,30,40,50,60,70,80,90,100,150,',',')
如果有函數A:抓取這個字符串中','出現的次數
如果有函數B:抓取指定次數的','在字符串中的位置做起這類題很easy了
--@string:待查找字符串,@index:查找位置
returns smallint
as
begin
declare
@i tinyint,--当前找到第@i个
@position tinyint--所在位置
set @position=@index;
set @i=0;
while charindex(@char,@string,@position)>0
begin
set @position=charindex(@char,@string,@position)+1;
set @i=@i+1;
if @i=@index
begin
return @position-1;
end
end
return 0;--0表示未找到
end
select dbo.char_index('10,20,30,40,50,60,70,80,90,100,150,',',',5) --,号第五次出现的位置 select stuff('10,20,30,40,50,60,70,80,90,100,150,',m,n,'被替换为字符')
set @s='10,20,30,40,50,60,70,80,90,100,150,'
--假如将第五个逗号前的50修改为55
--1.直接修改
set @s=STUFF(@s,charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1,CHARINDEX(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1)-(charindex(',',@s,charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1),'55')
select @s
/*
10,20,30,40,55,60,70,80,90,100,150,
*/
go
--2.存储过程修改
if OBJECT_ID('sp_upstr') is not null
drop proc sp_upstr
go
create proc sp_upstr(
@s nvarchar(max),--输入字符
@flag nvarchar(10),--分隔符
@n int,--取第n个分隔符与第n-1个分隔符前的字符
@r nvarchar(50) --替换值
)
as
begin
declare @s1 nvarchar(max),@i int,@j int,@k int
select @s1=@flag+@s,@i=0,@j=0,@k=0
while @i<@n
begin
set @k=@j
set @j=@j+LEN(left(@s1,CHARINDEX(@flag,@s1)))
set @s1=stuff(@s1,1,charindex(@flag,@s1),'')
set @i=@i+1
end
if @n=1
set @s=STUFF(@s,@j,CHARINDEX(@flag,@s)-1,@r)
else
set @s=STUFF(@s,@j,@j-@k-1,@r)
select @s
end
go
--调用过程
declare @s nvarchar(max)
set @s='10,20,30,40,50,60,70,80,90,100,150,'
exec sp_upstr @s,',',5,'55'
/*
10,20,30,40,55,60,70,80,90,100,150,
*/
insert into #tt
select '1'
union Select '2'
union Select '3'
union Select '4'
union Select '5'
union Select '6'
declare @s varchar(100),@i int,@index intSelect @s= '10,20,30,40,50,60,70,80,90,100,150,', @i=1,@index=0Update t set @index=charindex(',',@s),@s=stuff(@s,charindex(',',@s),1, case @i when 4 then '~$!' else '~$' end ),
@i=@i+1 from #tt t
where t.tid<=5 and @i<=5Select Replace(Stuff(@s,charindex('~$!',@s),@index-charindex('~$!',@s),',999'),'~$',',')
Drop table #tt
--我也来一个呵呵