CODEWITHSUNDEEP
sql-servert-sqlnatural-sort
Malfist
Malfist

Reputation: 31805

Natural sort for SQL Server?

I have a column that is typically only numbers (sometimes it's letters, but that's not important).

How can I make it natural sort?

Currently sorts like this: {1,10,11,12,2,3,4,5,6,7,8,9}

I want it to sort like this: {1,2,3,4,5,6,7,8,9,10,11,12}

Upvotes: 8

Views: 3980

Answers (3)

DiGi
DiGi

Reputation: 2558

IsNumeric is "broken", ISNUMERIC(CHAR(13)) returns 1 and CAST will fail.

Use ISNUMERIC(textval + 'e0'). Final code:

ORDER BY
  PropertyName,
  CASE ISNUMERIC(MixedField + 'e0') WHEN 1 THEN 0 ELSE 1 END, -- letters after numbers
  CASE ISNUMERIC(MixedField + 'e0') WHEN 1 THEN CAST(MixedField AS INT) ELSE 0 END,
  MixedField

You can mix order parameters...

Upvotes: 8

ktharsis
ktharsis

Reputation: 3190

Cast it. Also, don't forget to use IsNumeric to make sure you only get the numbers back (if they include letters it IS important ;).

SELECT textval FROM tablename
WHERE IsNumeric(textval) = 1
ORDER BY CAST(textval as int)

Also, cast to the datatype that will hold the largest value.

If you need the non-numbers in the result set too then just append a UNION query where IsNumeric = 0 (order by whatever you want) either before or after.

Upvotes: 2

Bruie
Bruie

Reputation: 1235

Have you tied using:

'OrderBy ColumnName Asc'

at the end of your query.

Upvotes: -3

Related Questions