ramsey ramsey - 3 months ago 9
SQL Question

How to sort SQL query alphabetically but ignoring leading numbers?

I am unable to find the right query for my problem. I have a table in the db and I need to sort it in a very specific manner - the column I am sorting is an address, and it starts with the number, but I need to sort it ignoring the number.

Here is my data set:

id | address
1 | 23 Bridge road
2 | 14 Kennington street
3 | 7 Bridge road
4 | 12 Oxford street
5 | 9 Bridge road


I need to sort this like:

id | address
1 | 7 Bridge road
2 | 9 Bridge road
3 | 23 Bridge road
4 | 14 Kennington street
5 | 12 Oxford street


So far I got only this:

SELECT id, address
FROM propertySearch
Order by address ASC.


Can anyone help me out on this?

Answer

If this will always be that format(leading number, a space and then the address) , then you can do this:

SQL-Server:

SELECT * FROM YourTable t
ORDER BY SUBSTRING(t.address,CHARINDEX(' ',t.address,1),99)

MySQL :

SELECT * FROM YourTable t
ORDER BY SUBSTRING_INDEX(t.address,' ',-1) 

If the format is not constant , you can use SQL-Server patindex() :

SELECT * FROM YourTable t
ORDER BY SUBSTRING(t.address,PATINDEX('%[A-z]%',t.address),99)

NOTE: This is bad DB design!! Each value should be properly stored in its own column, E.G STREET , CITY , APARTMANT_NUMBER ETC, becuase if not, they are leading to exactly this.