Natural collation for SQL Server?

Question

I have a column, which is usually a number (sometimes it's letters, but that doesn't matter).

How can I make it natural?

Currently, the following is being executed: {1,10,11,12,2,3,4,5,6,7,8,9}

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

Answer 1

IsNumeric is broken, ISNUMERIC (CHAR (13)) returns 1, and CAST will not be executed.

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 ...

Answer 2

Throw it. Also, be sure to use IsNumeric to make sure you only return the numbers (if they include letters, this is important;).

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

It also applies to the data type that will contain the highest value.

If you do not need numbers in the result set, just add a UNION query, where IsNumeric = 0 (order by whatever you want) before or after.

Answer 3

Are you connected with:

'OrderBy ColumnName Asc'

at the end of your request.