Sort field that contains numbers and letters

Question

I have a list of numbers and number-letter and letter-number.The type of my column is string .My data is like this :

1
14
3
S-34
2
36-1/E
26
S-14
20
S-2
19
36-1
30
35
S-1
34

but i want to sort like this :

1
2
3
14
20
25
30
35
36-1
36-1/E
S-1
S-2
S-14
S-34

But my code sort the data like this :

1
14
19
2
20
25
3
30
35
36-1
36-1/E
S-1
S-14
S-2
S-34

my code is:

List<ViewTestPackageHistorySheet> lstTestPackageHistorySheets = _reportTestPackageHistorySheetRepository.ShowReport(Id).OrderBy(i => i.JointNumber).ToList();

I changed the code below but failed.

List<ViewTestPackageHistorySheet> lstTestPackageHistorySheets = _reportTestPackageHistorySheetRepository.ShowReport(Id).OrderBy(i => Convert.ToInt32(i.JointNumber)).ToList();

Error is:

LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method, and this method cannot be translated into a store expression.

Custom Alphanumeric sorter, which checks whether two elements are number then compare the values else do a string comparison, voila and you will get the required output — Mrinal Kamboj, Aug 18 '16 at 07:16
http://stackoverflow.com/questions/2619617/how-to-sort-number-in-alphanumeric — sharno, Aug 18 '16 at 08:47
Possible duplicate of [Sorting mixed numbers and strings](http://stackoverflow.com/questions/1032775/sorting-mixed-numbers-and-strings) — Lennart, Aug 18 '16 at 10:17

Mrinal Kamboj · Accepted Answer · 2016-08-18T10:30:59.517

Use the following Alphanumericsorter, which use the PInvoke internally

public class AlphaNumericSorter : IComparer<string>
{
    public int Compare(string x, string y)
    {
        return SafeNativeMethods.StrCmpLogicalW(x, y);
    }
}

[SuppressUnmanagedCodeSecurity]
internal static class SafeNativeMethods
{
    [DllImport("shlwapi.dll", CharSet = CharSet.Unicode)]
    public static extern int StrCmpLogicalW(string psz1, string psz2);
}

Usage:

List<string> testList = // Your input list;

testList.Sort(new AlphaNumericSorter());

Now testList contains the sorted data as expected in the question

score 1 · Answer 2 · answered Aug 18 '16 at 07:35

Create a custom Comparer<string> and implement your desired logic (Of course it's inefficient for large lists and you should tune it):

 var data = list.OrderBy(x => x, new SpecialComparer());

SpecialComparer

   public class SpecialComparer : IComparer<string>
        {
            public int Compare(string x, string y)
            {
                if (x.Equals(y))
                    return 0;

                int yn;
                int xn;
                bool isXNumber = int.TryParse(x, out xn);
                bool isYNumber = int.TryParse(y, out yn);

                if (isXNumber && isYNumber)
                {
                    return xn.CompareTo(yn);
                }
                else if (isXNumber && !isYNumber)
                {
                    return -1;
                }
                else if (!isXNumber && isYNumber)
                {
                    return 1;
                }
                else if(x.Any(c => c == '/' || c == '-' ) || y.Any(c => c == '/' || c == '-'))
                {
                    var xParts = x.Split("/-".ToCharArray(),  StringSplitOptions.RemoveEmptyEntries);
                    var yParts = y.Split("/-".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);

                    var minLen = Math.Min(xParts.Length, yParts.Length);

                    var result = 0;
                    for (int i = 0; i < minLen; i++)
                        if ((result = Compare(xParts[i], yParts[i])) != 0)
                            return result;

                    return x.Length < y.Length ? -1 : 1;
                }
                else
                {
                    return x.CompareTo(y);
                }
            }
        }

I get this error :Severity Code Description Project File Line Error CS0411 The type arguments for method 'Enumerable.OrderBy(IEnumerable, Func, IComparer)' cannot be inferred from the usage. Try specifying the type arguments explicitly — Mehrdad Ghaffari, Aug 18 '16 at 10:38
here is my code: _reportTestPackageHistorySheetRepository.ShowReport(Id).ToList().OrderBy(x=>x, new SpecialComparer()); — Mehrdad Ghaffari, Aug 18 '16 at 10:39
Take this list - `"1017", "650", "650C", "650B", "W323", "10", "20", "1000", "W1000", "W900" `, the solution posted above doesn't work for elements starting with Alphabet, it shall result in `W323, W900, W1000`, but solution result is `W1000, W323, W900`, since we are not comparing each and every character — Mrinal Kamboj, Aug 18 '16 at 10:50
@MrinalKamboj, I only fitted the solution to the problem description. There's no data item having a letter followed by digits without a whitespace character — Mohsen Afshin, Aug 18 '16 at 12:19

score 1 · Answer 3 · answered Aug 18 '16 at 10:01

1

Linq to Entities does not support Convert.ToInt32 that's what is causing an error in your case, I would suggest materialize the query and then apply your ordering.

List<ViewTestPackageHistorySheet> lstTestPackageHistorySheets = _reportTestPackageHistorySheetRepository.ShowReport(Id).ToList();

// Now order the list. 
lstTestPackageHistorySheets = lstTestPackageHistorySheets.Select(x=> 
             new 
             {   // split number and string
                 number =  Regex.IsMatch(x.Split('-')[0],@"^\d+$")? int.Parse(x.Split('-')[0]): int.MaxValue, 
                 item =x
             })
    .OrderBy(x=>x.number)    // first order by number
    .ThenBy(x=>x.item)       // then order by string. 
    .Select(x=>x.item)
    .ToList();

Check this Demo

answered Aug 18 '16 at 10:01

Hari Prasad

16,716
4
21
35

Take this list - `"1017", "650", "650C", "650B", "W323", "10", "20", "1000", "W1000", "W900" `, the solution posted above doesn't work for elements starting with Alphabet, it shall result in `W323, W900, W1000`, but solution result is `W1000, W323, W900`, since we are not comparing each and every character – Mrinal Kamboj Aug 18 '16 at 10:47
Ahh... you should have explain that in the question, based on your example I thought there is a delimiter if it starts with number, but remember idea is same. You just need to extract the number if it starts with number. – Hari Prasad Aug 18 '16 at 11:45
I am not the OP :), nonetheless, you may correct the answer – Mrinal Kamboj Aug 18 '16 at 11:53

Sort field that contains numbers and letters

3 Answers3