拆分字符串
A表
type 字段
08-04-aa-bb
每个字段有3个-,固定的
我想拆分他
变成4个字段,字段名称无所谓,变成n1,n2,n3,n4也行
[解决办法]
create function[dbo].[stringsplit](@strnvarchar(max),@spliternvarchar(10))
returns@tbtable(chnvarchar(256))
as
begin
declare@numint,@posint,@nextposint
set@num=0
set@pos=1
while(@pos<=len(@str))
begin
select@nextpos=charindex(@spliter,@str,@pos)
if(@nextpos=0or@nextposisnull)
select@nextpos=len(@str)+1
insertinto@tbvalues(rtrim(ltrim(substring(@str,@pos,@nextpos-@pos))))
select@pos=@nextpos+1
end
return
end
SELECT * from [dbo].[StringSplit] ('08-04-aa-bb' ,'-')
[解决办法]
with tb(a) as(
select '08-04-aa-bb'),
tbb (a)as (
select RIGHT(a,len(a)-CHARINDEX('-',a))from tb
),
tbbb(a)as(
select RIGHT(a,len(a)-CHARINDEX('-',a))from tbb
),
tbbbb(a)as(
select RIGHT(a,len(a)-CHARINDEX('-',a))from tbbb
)
select LEFT(a,charindex('-',A)-1) from tb union all
select LEFT(a,charindex('-',A)-1) from tbb union all
select LEFT(a,charindex('-',A)-1) from tbbb union all
select * from tbbbb
with tb(a) as( select '08-04-aa-bb'),
tbb (a)as ( select RIGHT(a,len(a)-CHARINDEX('-',a))from tb ),
tbbb(a)as( select RIGHT(a,len(a)-CHARINDEX('-',a))from tbb ),
tbbbb(a)as( select RIGHT(a,len(a)-CHARINDEX('-',a))from tbbb )
select LEFT(tb.a,charindex('-',tb.A)-1)n1,n2,n3,tbbbb.a n4 from tb left join
(select LEFT(tbb.a,charindex('-',tbb.A)-1)n2 from tbb)b on 1=1 left join
(select LEFT(tbbb.a,charindex('-',tbbb.A)-1)n3 from tbbb)c on 1=1 left join
tbbbb on 1=1
