Order mixed string/number column with Entity Framework

Question

In my database, I have a column which can contain either only numbers, or text (which can also contain numbers). For char columns, SQL server sorts value by character code (1, 12, 14, 2, 20, 200, 3, 30, ...).

How would I have to write my .OrderBy statement to sort them like numerics, when they are numeric?

Answer 1

If you want to sort on DB ( not on application ), then

use patindex() and substring() functions to extract the integer part of your column( considering null cases as zero ), and then cast as integer :

Order By cast( coalesce(
                substring(yourCol, patindex('%[0-9]%', yourCol), 
                   patindex('%[0-9][^0-9]%', yourCol + 't') 
                   - patindex('%[0-9]%', yourCol) + 1)

               ,0)  as int )

within your select statement.

Demo