AlphaNumeric ordering in SQL vs. LINQ and .NET

I encountered an interesting thing today that I have never noticed before. It appears that SQL and LINQ order AlphaNumeric strings differently.

Data table contains rows: A G 6 P 1 D J 2 T Z 9 F 0

If I perform an Order By in the SQL, I receive the following results: A D F G J P T Z 0 1 2 6 9

Now consider this LINQ sample:

class Program
{
    static void Main(string[] args)
    {
        var list = new List<string>()
                       {
                           "A",
                           "G",
                           "6",
                           "P",
                           "1",
                           "D",
                           "J",
                           "2",
                           "T",
                           "Z",
                           "9",
                           "F",
                           "0"
                       };
        Console.WriteLine("Default Order:");
        list.ForEach(s => Console.WriteLine(s));

        Console.WriteLine();
        Console.WriteLine("Sorted Order:");
        foreach (string s in list.OrderBy(f => f))
        {
            Console.WriteLine(s);
        }                        
    }
}

The output for this is 0 1 2 6 9 A D F G J P T Z

So where SQL places Letters first and Numbers 2nd, LINQ orders Numbers first and Letters 2nd. I put these results in a DataGrid and clicked the header, and sure enough it also orders ala LINQ, so this could be a deeper divide, like at the .NET/Windows level.

The problem I have is that my users expect the ordering behavior they are used to seeing as a result of SQL ordering. How do I get LINQ to behave the same way?

UPDATE

The answer is flagged below, but just for anyone who stumbles onto this later, I wanted to recap, because it took a combination of answers to solve the problem.

1) dcp went immediately where my brain didn't: the Data Source. The problem turns out to be the difference between IBM's SQL, which uses EBCDIC sorting, and every other technology in the known universe using ASCII sorting. Thanks IBM for once again reminding me why I a开发者_JAVA技巧m now a .NET developer.

2) Recognizing this, Ahmad provided a very elegant solution that also eluded me: a custom IComparer<string>. I used the code he provided and it sorted the List as desired.

Thanks to both StackOverflow comes through again!

UPDATE 2

After yesterdays posting I got this finished and wanted to share the final results.

The post yesterday was a simple list of single characters, but in reality these were embedded in longer strings. To make this work with longer strings, I changed the original string comparer to a char comparer, and then looped through the strings and compared each character until I either found a mismatch or ran out of characters to compare. Here are the final two Comparer classes:

public class EbcdicCharComparer : IComparer { public int Compare(char x, char y) { int xNum, yNum; bool xIsNum = Int32.TryParse(x.ToString(), out xNum); bool yIsNum = Int32.TryParse(y.ToString(), out yNum);

    // compare numbers
    if (xIsNum && yIsNum)
    {
        return xNum.CompareTo(yNum);
    }

    // compare num to char
    if (xIsNum)
    {
        return 1;
    }

    // compare num to char
    if (yIsNum)
    {
        return -1;
    }

    // compare normally
    return x.CompareTo(y);

}

}

public class EbcdicStringComparer : IComparer { public int Compare(string x, string y) { var xArr = x.ToCharArray(); var yArr = y.ToCharArray();

    var iterations = xArr.Length > yArr.Length ? yArr.Length : xArr.Length;
    var charComp = new EbcdicCharComparer();
    for (int i = 0; i < iterations; i++)
    {
        var compValue = charComp.Compare(xArr[i], yArr[i]);
        if (compValue != 0)
            return compValue;
    }

    // compare as strings
    return x.CompareTo(y);
}

}

The real data is more like this:

• 024 A 17
• 024 A 18
• 024 A 19
• 024 1 19
• 024 C 19A
• 024 3 3
• 024 A 3B

And as desired, the comparer now returns the data in this order:

• 024 A 3B
• 024 A 17
• 024 A 18
• 024 A 19
• 024 C 19A
• 024 1 19
• 024 3 3

Thanks again to all who helped.

You could use the overloaded OrderBy method that accepts an IComparer. The IComparer would compare items to yield the desired result:

public class CustomComparer : IComparer<string>
{
    public int Compare(string x, string y)
    {
        double xNum, yNum;
        bool xIsNum = Double.TryParse(x, out xNum);
        bool yIsNum = Double.TryParse(y, out yNum);

        // compare numbers
        if (xIsNum && yIsNum)
        {
            return xNum.CompareTo(yNum);
        }

        // compare num to string
        if (xIsNum)
        {
            return 1;
        }

        // compare num to string
        if (yIsNum)
        {
            return -1;
        }

        // compare as strings
        return x.CompareTo(y);
    }
}

Usage:

list.OrderBy(i => i, new CustomComparer())

The implementation could've used Int32.TryParse for your specific sample data, however I used Double just in case you had a "3.0" in the list or such. Of course, you need to be aware of your data. If the value is too large you'll need to use the appropriate TryParse method.

On the databases I've tried it on (Oracle and SQL Server 2008) it sorts using ASCII ordering (numbers before letters the same as the LINQ query). Are you using some other database?

SQL Server Query:

SELECT 'A' col1 
UNION ALL
SELECT 'B' col1 
UNION ALL
SELECT '0' col1 
ORDER BY 1

Rows come back as 0, A, B

Oracle Query:

SELECT 'A' col1 FROM dual
UNION ALL
SELECT 'B' col1 FROM dual
UNION ALL
SELECT '0' col1 FROM dual
ORDER BY 1

Rows come back as 0, A, B