Linq to SQL - How to order numbers as strings?

Question

I have a SQL varchar column that has values like 100, 2000 and S5000. I want them ordered numerically rather than alphabetically. If there is a string character I am happy for the character to be either ignored or appear after the numeric values in the sort order.

In T-SQL I could this this with:

SELECT * FROM tbl
ORDER BY
  CASE
    WHEN ISNUMERIC(fld) = 1 THEN CONVERT(INT, fld)
    ELSE 2147483647
    END ASC,
  fld ASC

I am wondering if there is a way to do this in Linq to SQL?

Otherwise I guess my alternatives are to execute the query directly, or create a calculated column with values like 00000100, 00002000,000S5000, etc.

Edit: I have found a solution although I am not sure how efficient it is.

from s in q
orderby (SqlMethods.Like(s.fld, "%[^0-9]%") ? Int32.MaxValue : Convert.ToInt32(s.fld)) ascending, s.fld ascending
select s;

You might want to check out this question: http://stackoverflow.com/questions/2329580/t-sql-isnumeric-and-linq-to-sql - it should provide a start. — mal-wan, Aug 25 '11 at 00:55
You should add your answer you found as an actual answer below and mark it as the answer if that's what you go with and upvote any answers that you found helpful. — ckittel, Aug 26 '11 at 01:09

score 2 · Accepted Answer · answered Jan 09 '12 at 01:20

2

I have found a solution although I am not sure how efficient it is.

from s in q orderby (SqlMethods.Like(s.fld, "%[^0-9]%") ? Int32.MaxValue : Convert.ToInt32(s.fld)) ascending, s.fld ascending select s;

answered Jan 09 '12 at 01:20

johna

10,540
14
47
72

score 1 · Answer 2 · answered Aug 25 '11 at 02:41

1

If you've already returned the results from the database and you're happy to do the sorting in memory then here's a way to go.

First, define a tryParse function that can be used in a linq-to-objects query:

Func<string, int> tryParse = s =>
{
    int i;
    if (!int.TryParse(s, out i))
    {
        i = int.MaxValue;
    }
    return i;
};

Then the actual query to do the sort is simple:

var query =
    from t in tbls.ToArray() // force the linq-to-sql query to execute
    orderby t.fld
    orderby tryParse(t.fld)
    select t;

Easy, huh?

answered Aug 25 '11 at 02:41

Enigmativity

113,464
11
89
172

+1, but did you really mean to have both `orderby` statements there? Only the latter is necessary, right? – ckittel Aug 25 '11 at 15:26
@ckittel - I added the first one to order the non-parsable fields. It's not strictly necessary, but it makes all the data come back in a defined order. – Enigmativity Aug 25 '11 at 23:01
Thanks but I was hoping on doing this in the database as I am only returning a page of results from a large table. – johna Aug 26 '11 at 00:38

score 0 · Answer 3 · answered Aug 25 '11 at 14:04

I know the question was about Linq-to-SQL, but I thought I would provide an Entity Framework 4 answer for any EF users that stumbled over this question looking for a way to do this within that framework. I have no experience with Linq-to-SQL, so, for all I know this would actually work there as well (but I doubt it).

Dim results = (From item in ctx.tbl
               Let sortValue = If(SqlFunctions.IsNumeric(item.fld) = 1, CInt(item.fld), 2147483647)
               Order By sortValue).ToList()

This turns roughly into this store query:

SELECT [Project1].*
FROM ( SELECT 
    [Extent1].*
    CASE WHEN (1 = (ISNUMERIC([Extent1].[Value]))) THEN  CAST( [Extent1].[Value] AS int) ELSE 99999999 END AS [C2]
    FROM [dbo].[tbl] AS [Extent1]
)  AS [Project1]
ORDER BY [Project1].[C2] ASC

This solution leverages System.Data.Objects.SqlClient.SqlFunctions to basiclly directly invoke the SQL ISNUMERIC function in the query. I do want to point out that IsNumeric will return 1 even for decimals, and casting a "decimal" varchar into an int will cause a problem. It might be safest to CDbl() instead of CInt()ing the value, unless you know your data will never be anything but an "int" varchar.

That looked like a great solution but IsNumeric does not appear to be supported by Linq to SQL, error was "System.Nullable`1[System.Int32] IsNumeric(System.String)' has no supported translation to SQL." Very good advice over the convert to double rather than integer. — johna, Aug 26 '11 at 00:33
Yeah, figured it wouldn't be a direct help to you. Hopefully someone else finds it useful. — ckittel, Aug 26 '11 at 00:49

Linq to SQL - How to order numbers as strings?

3 Answers3