ramsey ramsey - 1 year ago 70
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 Source

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


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


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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download