SQL – Ordering Alphanumeric columns numerically when they contain mostly numeric data

This came up when a customer wanted a House Number address column to be ordered numerically but it contained alphanumeric data (nvarchar, containing values like “1”, “2A”). Standard alphanumeric ordering made results look strange, since the values were mostly numeric (e.g. 1, 11, 111, 2, etc).

I thought that correcting this would be very difficult and would slow the query down considerably, but it turned out to be quite simple.

 

 

SELECT * FROM [app_Address]

ORDER BY CASE ISNUMERIC(HouseNumber) WHEN 1 THEN REPLICATE(‘0’, 90 – LEN(HouseNumber)) + HouseNumber ELSE HouseNumber END

 

 

* Where 90 is the size of the alphanumeric column

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s