Order by clause

Transact-SQL
1

Hello, we have develop a builder web app and here we stores plot nos of particular layout.
My client says that show all plots in ascending order. But plots are not always numeric value
it could be like

1
2
3+4
10 and 11

also I can't assign serial no like any field to this because user can edit or add later on new value
like in my case after entering above data he can add later on -
5
6
7,8,9

So, is there any way to show this data in ascending order
I tried
select * from tablename Order By CAST(PlotNumber as Int) ASC
but gives an error. Can anyone suggest good solution

2

There is no easy way to order the way you want it to. If you simply order by the column, it would do a alphabetic sort, which is probably not what you want. You can try the following.

ORDER BY 
	PATINDEX('%[^0-9]%',PlotNumber +'x'),
		STUFF(PlotNumber +'x',PATINDEX('%[^0-9]%',PlotNumber +'x'),100,'')
1 Like
3

Hey JamesK,
It solved my issue. Thanking you! Well done