Clever Geek Handbook

SQL order address by house number

So if I have results

11 The street
9 The street 
59 The street

Regular ORDER BY ASC

does it

11 The street
59 The street 
9 The street

How can I order ASC

and count numbers like

9 The street
11 The street 
59 The street
0


source to share


2 answers


Try:

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

SQL Fiddle Demo



For MySql, please try:

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

MySql Fiddle

+2


source


Another solution (a bit simple); just going to your sample data and assuming all your data lines have in The Street

common in them. Otherwise what TechDo suggested should be the one I go to (for a general solution)



select * from tbl
order by 
cast(
(substring(col,0,charindex('The street',col))) as int)
0


source






More articles:


All Articles