1

Using LINQ or SQL, how could I have the following strings, normally sorted as:

"banana"
apple
coconut

Sort as:

apple
"banana"
coconut
double-beep
  • 5,031
  • 17
  • 33
  • 41
mattucg
  • 13
  • 3
  • Well, you're asking SQL or LINQ to ignore the leading special character, so you'll need some form of regular expression, and you may end up loading the table into memory to clean up the data. It sounds risky. –  Nov 30 '10 at 21:31

3 Answers3

0

Updated based on your comment

IList<string> sorted = context.Terms.ToList()
    .OrderBy(t => Regex.Replace(t.Term, @"\W*","")).ToList();
hunter
  • 62,308
  • 19
  • 113
  • 113
  • I like your idea, but am unsure how to proceed. How would I apply your suggestion to the following: var query = from term in context.Terms orderby term.Term select term; – mattucg Nov 30 '10 at 22:35
0

On SQL you can sort it without needing REGEX functions per se, you can use PATINDEX. Try this:

SELECT *
FROM Table
ORDER BY RIGHT(Column,LEN(Column)-patindex('%[a-zA-Z]%',Column)+1)

This way you are sorting the Table using the first letter of the column, ignoring the others characters

Lamak
  • 69,480
  • 12
  • 108
  • 116
  • This works perfectly, thanks. I can only accept one answer, and I ended up using LINQ, but I appreciate your help. – mattucg Dec 01 '10 at 16:16
0

You could add a column which contains just the alphanumeric string, then sort on this.

The function RemoveNonAlphaCharacters found here will allow you to filter out non alphanumeric characters. If the table is very small and performance is not a problem, you could simply

ORDER BY RemoveNonAlphaCharacters(columnToClean)
Community
  • 1
  • 1
John
  • 5,672
  • 7
  • 34
  • 52