排序sql

[caoyang0299]

字段a，值
1.........
23.......
74.......
5.........
想顺序排下，sql应该怎么写下
1..........
2............
10........
23,,,,,,,,

[卖水果的net]

create table test(a varchar(30))
go
insert into test(a) 
values('1.........'),('23.......'),('74.......'),('5.........')
go
select * from test 
go
select * from test order by cast(REPLACE(a,'.','') as int)
go
drop table test 
go


(4 行受影响)
a
------------------------------
1.........
23.......
74.......
5.........

(4 行受影响)

a
------------------------------
1.........
5.........
23.......
74.......

(4 行受影响)

[caoyang0299]

弄错啦............这个是省略号，前面是个自然数，后面是字符，想按前面数字部分排序

[LongRui888]

引用 2 楼 caoyang0299 的回复:

弄错啦............这个是省略号，前面是个自然数，后面是字符，想按前面数字部分排序

原理是一样的： select * from test order by cast(REPLACE(a,'……','') as int) 也就是用replace函数把省略号替换成空串，然后转化为数值后 ，进行排序就可以了

[freecodex]

; WITH cte(col) AS (
SELECT '1abbdd' UNION ALL
SELECT '23fffk' UNION ALL
SELECT '74ppooo' UNION ALL
SELECT '4ffddr'
)
SELECT col FROM cte ORDER BY CAST(LEFT(col,PATINDEX('%[a-z]%',col) - 1)  AS INT) ASC 

col
-------
1abbdd
4ffddr
23fffk
74ppooo

[caoyang0299]

用replace，这个replace应该咋写

[Cherise_huang]

create table test
(
	a varchar(30)
)
go
insert into test(a) 
values('1dsa喔sf'),('23safdsaf'),('74asfdf'),('5ghfh'),('10sdgh')
GO

IF OBJECT_ID('fn_getAllNumFromCharacter','FN') is not null drop function fn_getAllNumFromCharacter
go
create function fn_getAllNumFromCharacter(@Param varchar(1024))
returns varchar(1024)
as
Begin
/*通过循环的方式，每个字符逐个截取*/
--判断是否存在字符
while(patindex('%[^0-9]%',@Param))>=1
begin
--把字符换成''（空）
set @Param=STUFF(@Param,patindex('%[^0-9]%',@Param),1,'')
end
return @Param
End
go

SELECT A   FROM test ORDER BY convert(int,dbo.fn_getAllNumFromCharacter(A) )

结果如图：

[Cherise_huang]

[Cherise_huang]

用函数可以实现循环动态截取，根据字符前的数字进行排序

[Landa_Tiny_cui]

楼上回答的真棒