How to sort SQL query alphabetically, but ignoring leading numbers?

Question

I cannot find the correct request for my problem. I have a table in db and I need to sort it in a very specific way - the column that I am sorting is the address, and it starts with a number, but I need to sort it by ignoring the number.

Here is my dataset:

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 as:

 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 only got this:

 SELECT id, address FROM propertySearch Order by address ASC. 

Can someone help me with this?

Answer 1

If it always will be this format (leading number, space, and then address), 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 persistent, you can use SQL-Server patindex() :

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

NOTE: This is a bad DB design! Each value must be correctly stored in its own column, EG STREET , CITY , APARTMANT_NUMBER ETC, because if not, they lead to exactly that.

Answer 2

If you are using SQL Server, you can use a combination of PATINDEX and STUFF :

 SELECT *, STUFF(T.address, 1, PATINDEX('%[Az]%', T.address) - 1, '') FROM #Table1 AS T ORDER BY STUFF(T.address, 1, PATINDEX('%[Az]%', T.address) - 1, '') 

PATINDEX will find the index of the first letter in your string and STUFF used to trim everything from the beginning to this index.

This conclusion:

 id address No column name) --------------------------------------------- 1 23 Bridge road Bridge road 3 7 Bridge road Bridge road 5 9 Bridge road Bridge road 2 14 Kennington street Kennington street 4 12 Oxford street Oxford street 

I also noticed that you have a different order in the expected output. If it was conceived. You need to use ROW_NUMBER:

 SELECT ROW_NUMBER() OVER(ORDER BY STUFF(T.address, 1, PATINDEX('%[Az]%', T.address) - 1, ''), T.id) AS ID, T.address FROM #Table1 AS T; 

This request will generate a new identifier for each row.
Result:

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

---

In any case, this is a pretty hacky decision.

I suggest you keep your address in separate columns, such as street name, zip code, house number, home letter (optional), city, etc. This will be a much better approach.

Answer 3

I think this type of operation is more suitable for the business level.
If you download all the data in .net code, the sorting will be simpler, more readable, and more maintainable.

 Public Class Address Public Property Id As Integer Public Property AddressData As String 'This property can be used for sorting Public ReadOnly Property SortedKey As String Get Dim rawData As IEnumerable(Of String) = Me.AddressData.Split(" "c).Skip(1) Return String.Join(" ", rawData) End Get End Property End Class 

Then use it with LINQ

 Dim loaded As List(Of Address) = yourLoadFunction() Dim sorted = loaded.OrderBy(Function(item) item.SortedKey).ToList() 

Answer 4

As you noted vb.net, suppose you are using MS SQL. If you always separate the street number and the street name with empty space, try the following:

ORDER BY RIGHT([address], LEN([address]) - CHARINDEX(' ', [address], 1))

Answer 5

 Declare @Table table (id int,address varchar(100)) Insert into @Table values (1,'23 Bridge road'), (2,'14 Kennington street'), (3,'7 Bridge road'), (4,'12 Oxford street'), (5,'9 Bridge road') Select * From @Table Order By substring(address,patindex('%[az]%',address),200) ,cast(substring(address,1,charindex(' ',address)) as int) 

Returns

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