sql 字母排序有关问题
sql 字母排序问题
假如我有一字符串CABD，我想把这个字符串按照字母顺序变成ABCD，sql语句，需要怎么写，小弟先谢各位了。
------解决方案--------------------
declare @str nvarchar(100) = 'CABD'
declare @result nvarchar(100) = ''
declare @temp table
(
[Char] char
)
while(LEN(@str) > 0)
begin
insert into @temp
select LEFT(@str, 1)
set @str = SUBSTRING(@str,2,LEN(@str)-1)
end
select @result = @result+[Char] from @temp order by [Char]
select @result
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
---------------------
ABCD
(1 行受影响)
假如我有一字符串CABD，我想把这个字符串按照字母顺序变成ABCD，sql语句，需要怎么写，小弟先谢各位了。
------解决方案--------------------
declare @str nvarchar(100) = 'CABD'
declare @result nvarchar(100) = ''
declare @temp table
(
[Char] char
)
while(LEN(@str) > 0)
begin
insert into @temp
select LEFT(@str, 1)
set @str = SUBSTRING(@str,2,LEN(@str)-1)
end
select @result = @result+[Char] from @temp order by [Char]
select @result
------解决方案--------------------
declare @s varchar(1000)
set @s='CABDKZSVX'
select chr
from
(
select substring(chr,number+1,1) as chr
from (select @s as chr)a,master..spt_values
where type='P' and number<len(chr)
)t
order by chr
/*
chr
------------------------
A
B
C
D
K
S
V
X
Z
*/
------解决方案--------------------
declare @x varchar(10),@y varchar(10)
select @x='CABD'
select @y=cast(
(select ''+c from
(select substring(@x,number,1) 'c'
from master.dbo.spt_values
where type='P' and number between 1 and len(@x)) t
order by c for xml path('')) as varchar(10))
select @y 'y'
/*
y
----------
ABCD
(1 row(s) affected)
*/
------解决方案--------------------
WITH a1 AS
(
SELECT 'CABD' cstr
)
,a2 AS
(
SELECT cstr,SUBSTRING(cstr,1,1) cstr2,1 n FROM a1
UNION ALL
SELECT cstr,SUBSTRING(cstr,n+1,1),n+1 FROM a2
WHERE n+1<=LEN(cstr)
)
SELECT cstr=(SELECT ''+cstr2 FROM a2 ORDER BY cstr2 FOR XML PATH(''))
------解决方案--------------------
declare @t varchar(300)
set @t= 'CABD'
;with t
as
(
select @t as col,
substring(@t,s.number,1) as v
from master..spt_values s
where s.number >=1
and s.type = 'P'
and s.number <= LEN(@t)
)
select distinct
replace(
(select ','+v
from t t2
where t1.col = t2.col
order by v
for xml path('')
),',',''
) as v
from t t1
/*
v
ABCD
*/
------解决方案--------------------
declare @a varchar(200),@i int,@m varchar(200),@n int
set @a='CABD'
set @i=1
set @n=1
-- select substring(@a,@i,1)
while @i<len(@a)
begin
while @n<len(@a)
begin
if substring(@a,@n,1)>=substring(@a,@n+1,1)
begin
set @m=isnull(left(@a,@n-1),'')+substring(@a,@n+1,1)+substring(@a,@n,1)+isnull(right(@a,len(@a)-1-@n),'')
set @n=@n+1
-- select @i-1,@m,'1'
end
else
begin
set @m=isnull(left(@a,@n-1),'')+substring(@a,@n,1)+substring(@a,@n+1,1)+isnull(right(@a,len(@a)-1-@n),'')
set @n=@n+1
-- select @i-1,@m,'2'
end
set @a=@m
end
-- select @a,'3'
set @n=1
set @i=@i+1
end
select @a
---------------------
ABCD
(1 行受影响)