SQL字符串节取

[ivanrong]

有一字符串,如'aa¤bb㊣cc¤dd'.如何用纯SQL查询的方法(不能用存储过程、游标等等)

得出下列结果:

name
'aa'
'bb'
'cc'
'dd'

[sunsunup]

select substring(mpp_code,1,charindex('㊣',mpp_code,1)-1)as one,
substring(mpp_code,charindex('㊣',mpp_code,1)+1,charindex('㊣',mpp_code,2)-1) as two,
substring(mpp_code,charindex('㊣',mpp_code,2)+1,charindex('㊣',mpp_code,3)-1) as three,
substring(mpp_code,charindex('㊣',mpp_code,3)+1) as four
from
(select replace('a¤ab㊣abc¤abcd','¤','㊣') as mpp_code
)as a

[pengdali]

declare @你的字符参数 varchar(8000)
set @你的字符参数='aa¤bb㊣cfc¤dd'

declare @你的分割符 varchar(100)
set @你的分割符='¤㊣'


select substring(left(@你的分割符,1)+@你的字符参数,id+1,patindex('%['+@你的分割符+']%',substring(@你的字符参数,id+1,8000)+left(@你的分割符,1))) from
(select id from
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 id from
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4
) t5
where id between 1 and 8000) dali

where substring(left(@你的分割符,1)+@你的字符参数,id,8000) like '['+@你的分割符+']_%'
order by id

[pengdali]

select
left(mpp_code,patindex('%¤%㊣%',mpp_code)-1)as one

,right(mpp_code,charindex('¤',reverse(mpp_code))-1)as four

,substring(mpp_code,charindex('¤',mpp_code)+1,charindex('㊣',mpp_code)-charindex('¤',mpp_code)-1) two

,substring(mpp_code,charindex('㊣',mpp_code)+1,len(mpp_code)-charindex('㊣',mpp_code)-charindex('¤',reverse(mpp_code)))

from
(select 'a¤ab㊣abc¤abcd' as mpp_code
)as a

[steven8098]

楼上各位大哥，我对你们写的语句很佩服，我对此问题很感兴趣，我也写了一个，不知道对不对，呵呵
declare @a char
declare @b char
declare @i int
select @a = 'aa¤bb㊣cc¤dd'
select @i = 11
if (convert(int,ascii(substring(@a,1,1)))<65 or (convert(int,ascii(substring(@a,1,1)))>90
and (convert(int,ascii(substring(@a,1,1)))<97) or convert(int,ascii(substring(@a,1,1)))>122))
begin
set @a=substring(@a,2,@i-1)
set @i = @i-1
end
else
begin
set @b = substring(@a,1,2)
select @a = substring(@a,2,@i-2)
end

[pengdali]

declare @name varchar(8000)
set @name='aa¤bb㊣cfc¤dd'

select top 8000 identity(int,1,1) id into #dali from sysobjects a,sysobjects b

select id,substring(','+@name,id,8000),substring(','+@name,id+1,patindex('%[^A-z]%',substring(@name,id+1,8000)+',')) from #dali where substring(','+@name,id,8000) like '[^A-z]_%'
order by id
go
drop table #dali

[pengdali]

其中#dali你可以先建立好一个固定表。

declare @你的字符参数 varchar(8000)
set @你的字符参数='aa¤bb㊣cfc¤dd'

select top 8000 identity(int,1,1) id into #dali from sysobjects a,sysobjects b

--------------------------
select substring(','+@你的字符参数,id+1,patindex('%[^A-z]%',substring(@你的字符参数,id+1,8000)+',')) from #dali where substring(','+@你的字符参数,id,8000) like '[^A-z]_%'
order by id
----------------------------
go
drop table #dali

[pengdali]

如果你要一句搞定：

declare @你的字符参数 varchar(8000)
set @你的字符参数='aa¤bb㊣cfc¤dd'


select substring(','+@你的字符参数,id+1,patindex('%[^A-z]%',substring(@你的字符参数,id+1,8000)+',')) from
(select id from
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 id from
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4
) t5
where id between 1 and 8000) dali

where substring(','+@你的字符参数,id,8000) like '[^A-z]_%'
order by id

[youngby]

555555555555

学习ing

[txlicenhe]

把大力的稍改一下：

declare @你的字符参数 varchar(8000)
set @你的字符参数='aa¤bb㊣cfc¤dd'


select substring(','+@你的字符参数,id+1,patindex('%[^A-z]%',substring(@你的字符参数,id+1,8000)+',')) from
(select id from
(select t1.b+t2.b*10+t3.b*100+t4.b*1000 id from
(Select top 10 (select sum(1) from sysobjects where name<= a.name)-1 as b from sysobjects a) t1,
(Select top 10 (select sum(1) from sysobjects where name<= a.name)-1 as b from sysobjects a) t2,
(Select top 10 (select sum(1) from sysobjects where name<= a.name)-1 as b from sysobjects a) t3,
(Select top 10 (select sum(1) from sysobjects where name<= a.name)-1 as b from sysobjects a) t4
) t5
where id between 1 and 8000) dali

where substring(','+@你的字符参数,id,8000) like '[^A-z]_%'
order by id

[ivanrong]

很感谢你位的回复,但是都不是很符合我想要的.

我想要的是,根据我所给出的那个字符,分割成四个子串.
其实我是已经写了一个,但是太长啦,所以才上来请教一下.


select substring(substring(mpp_code,1,charindex('㊣',mpp_code)-1),1,charindex('¤',substring(mpp_code,1,charindex('㊣',mpp_code)-1))-1) as one
,substring(substring(mpp_code,1,charindex('㊣',mpp_code)-1),charindex('¤',substring(mpp_code,1,charindex('㊣',mpp_code)-1))+1,len(substring(mpp_code,1,charindex('㊣',mpp_code)-1))) as two
,substring(substring(mpp_code,charindex('㊣',mpp_code)+1,len(mpp_code)),1,charindex('¤',substring(mpp_code,charindex('㊣',mpp_code)+1,len(mpp_code)))-1) as three
,substring(substring(mpp_code,charindex('㊣',mpp_code)+1,len(mpp_code)),charindex('¤',substring(mpp_code,charindex('㊣',mpp_code)+1,len(mpp_code)))+1,len(substring(mpp_code,charindex('㊣',mpp_code)+1,len(mpp_code)))) as four
,left(mpp_code,patindex('%¤%㊣%',mpp_code)-1)as one
,right(mpp_code,charindex('¤',reverse(mpp_code))-1)as four
from
(select 'a¤ab㊣abc¤abcd' as mpp_code
)as a


上面的mpp_code是一个由另外一个查询生成的字符串.
我查出的 one ,two ,three ,four这四个子串将会用到另外的一个主查询中.
请各位试一下能否对上面我写的语句进行一定简化.其中第五、六两列是对应第一、四的简化,请写出对第二、三的简化.