@bobnoordam

Sql Server sorting with a partial numeric field

Posted on | By bobnoordam

Recently i came accross a database that stored customer numbers in a NVARCHAR field and wanted to sort on this value. By default this will not give you the order you want, since you will get a sort order like: 

1 10 11 2 20

etc. This can be easily overcome with the CONVERT statement. However, in this case the value in the field COULD also contain additional characters that altough not important for the sorting would still create an error since the field could no longer be converterd to its numerical counterpart.

The solution is to use the PATINDEX function to determine the numerical part, and sort on that portion of the fields. This technique is demonstrated below on the NorthWind sample database. To see what is the actual output of the CONVERT / PATINDEX combination, run this: 

use [Northwind]
select postalcode, CONVERT(INT, LEFT(PostalCode,PATINDEX('%[^0-9]%',PostalCode + ' ')-1)) from Customers

Applied to the PostalCode field in the NorthWind Database, the full syntax becomes:

use [Northwind]
select * from customers
order by
    CONVERT(INT, LEFT(PostalCode,PATINDEX('%[^0-9]%',PostalCode + ' ')-1)) desc
Category: SqlServer