0

So if I have results

11 The street
9 The street 
59 The street

A regular ORDER BY ASC does this 

11 The street
59 The street 
9 The street

How can I order by ASC and have the numbers count up like

9 The street
11 The street 
59 The street
David Faber
  • 12,277
  • 2
  • 29
  • 40
Guesser
  • 1,769
  • 3
  • 25
  • 52

2 Answers2

2

Please 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

TechDo
  • 18,398
  • 3
  • 51
  • 64
  • +1. I think he would want to sort by adress then number, though. Suggested edit: `select * From tbl order by Substring(Col, PatIndex('%[^0-9]%', Col), 999), cast(Left(Col, PatIndex('%[^0-9]%', Col)) as int) ` – Klas Lindbäck Apr 28 '14 at 11:07
  • Yes Klas that would make more sense – Guesser Apr 28 '14 at 11:25
  • @TechDo thanks I'm having trouble isolating the name of the street though now, to order by street name then number, The SUBSTRING_INDEX function doesn't seem capable of going right from a left starting point. See http://sqlfiddle.com/#!2/81a1d/1 – Guesser Apr 30 '14 at 11:04
  • @user1209203, maybe you should post that as an edit to the question? You don't mention needing to do that. – David Faber Jan 09 '15 at 15:00
0

Another solution (bit simple); just going by your sample data and assuming that all your data string have The Street common in them. Else, what TechDo have suggested should be the One I will go for (For a generic solution)

select * from tbl
order by 
cast(
(substring(col,0,charindex('The street',col))) as int)
Rahul
  • 76,197
  • 13
  • 71
  • 125