Scott Scott - 4 months ago 15
SQL Question

Ordering properties in MS SQL Server - including flats

I have been given a CAG and i'm trying to sort the address by Postcode and Building Number. The problem i face is that Building Number is not actually integer, it's a nvarchar- because of flat properties.

You can see that 79B appears after 143:

131
133A
133B
135
137
139
141
143
79B <--
87
89
91


I have found a similar question:
sql-for-ordering-by-number-1-2-3-4-etc-instead-of-1-10-11-12

and tried this

SQL += "ORDER BY BUILDNO * 1 ASC ";


but predictably i get a conversion error

Conversion failed when converting the nvarchar value '133A' to data type int.


Is it possible to order this type of nvarchar in SQL?

thanks

UPDATE

I know have this working, thanks @paYa

SELECT * FROM [" + tblname + "]
WHERE POSTCODE LIKE + @postcode + '%'
ORDER BY CAST(LEFT([BUILDNO], CASE WHEN PATINDEX(N'%[^0-9]%', [BUILDNO]) < 1 THEN LEN([BUILDNO]) ELSE PATINDEX(N'%[^0-9]%', [BUILDNO]) - 1 END) AS INT),
RIGHT([BUILDNO], LEN([BUILDNO]) - PATINDEX(N'%[^0-9]%', [BUILDNO]) + 1)


returns the correct order:

79B
87
89
91
133B
135
137
139
141
143

Answer

in mssql server

order by cast(left([ColName], case when patindex(N'%[^0-9]%', [ColName]) < 1 then len([ColName]) else patindex(N'%[^0-9]%', [ColName]) - 1 end) as int),
right([ColName], len([ColName]) - patindex(N'%[^0-9]%', [ColName]) + 1);