mysql - SQL 按门牌号订购地址

Question

如果我有结果

11 The street
9 The street 
59 The street

常规的ORDER BY ASC可以做到这一点

11 The street
59 The street 
9 The street

如何通过 ASC 订购并让数字像这样计数

9 The street
11 The street 
59 The street

Answer 1

请尝试:

select *
From tbl
order by cast(Left(Col, PatIndex('%[^0-9]%', Col)) as int)

SQL Fiddle Demo

对于MySql，请尝试:

select *
From tbl
order by convert(SUBSTRING_INDEX(Col, ' ', 1), UNSIGNED INTEGER)

MySql Fiddle