sql截断字符查询语句
有一字符串'10,102,10254,103265,541,2154,41,156',想写过sql语句查询第四个逗号之前的字符串'10,102,10254,103265'，求助各路路过高人指点
sql
[解决办法]
declare @str varchar(200)
set @str='10,102,10254,103265,541,2154,41,156'
;with cte as(
select LEFT(@str,number-1) as [str],ROW_NUMBER()over(order by getdate()) as [xh]
from master..spt_values
where number>=1 and type='P'
and SUBSTRING(@str+',',number,1)=','
)select [str] from cte where xh=4
-- 建函数
create function dbo.fn_substr
(@x varchar(100)) returns varchar(100)
as
begin
declare @a int,@b int
select @a=0,@b=0
while(@b<4)
begin
select @a=@a+1,
@b=@b+case substring(@x,@a,1) when ',' then 1 else 0 end
end
return substring(@x,1,@a-1)
end
-- 测试
declare @x varchar(100)
select @x='10,102,10254,103265,541,2154,41,156'
select dbo.fn_substr(@x) 'y'
/*
y
------------------------
10,102,10254,103265
(1 row(s) affected)
*/
declare @a varchar(50)
set @a='10,102,10254,103265,541,2154,41,156'+','
declare @tab table(name varchar(max))
insert into @tab
select @a
;with cte as
(
select
left(name,charindex(',',name)-1) as name,
right(name,len(name)-charindex(',',name)) as tesult
from @tab
union all
select
left(tesult,charindex(',',tesult)-1) as name,
right(tesult,len(tesult)-charindex(',',tesult)) as tesult
from cte
where len(tesult)>1
)
select stuff((
select top 4 ','+name from cte for xml path('')),1,1,'')
