The Datatable sorts into a sequence somthing like:
10-199
10-299
103-10
103-20
10-399
wheras I would like to sort in the form of
10-199
10-299
10-399
103-10
103-20
It seems that it ignores the '-' when sorting (trying to be helpful??).
Anyone got any ideas how I can get the Datatable to sort using exactly what
is in the column rather than applying any other 'rules' to it.
If I replace the '-' s with '~'s then it sorts fine, but that is not really
a solution.
If I replace the '-' s with '~'s then it sorts fine, but that is not really a solution.
A single quote (') or an apostrophe (') and hyphen (-) are considered last when SQL Server sorts data stored with Microsoft Windows collations. In other words, Windows collations ignore single quote and hyphen when ordering.
To work around this behavior, either use a binary comparison collation, or replace the '-' s with '~'s.
For example,
ORDER BY columnname COLLATE Latin1_General_Bin
ORDER BY REPLACE(columnname, '-', '~')
sruthi1234
Member
43 Points
37 Posts
Sorting Datatable With Hyphen / Dash vb.net
Oct 18, 2010 06:30 AM|LINK
hi,
The Datatable sorts into a sequence somthing like:
10-199
10-299
103-10
103-20
10-399
wheras I would like to sort in the form of
10-199
10-299
10-399
103-10
103-20
It seems that it ignores the '-' when sorting (trying to be helpful??).
Anyone got any ideas how I can get the Datatable to sort using exactly what
is in the column rather than applying any other 'rules' to it.
If I replace the '-' s with '~'s then it sorts fine, but that is not really
a solution.
Look forward to hearing any suggestions.
smirnov
All-Star
23694 Points
4053 Posts
Re: Sorting Datatable With Hyphen / Dash vb.net
Oct 18, 2010 08:51 AM|LINK
A single quote (') or an apostrophe (') and hyphen (-) are considered last when SQL Server sorts data stored with Microsoft Windows collations. In other words, Windows collations ignore single quote and hyphen when ordering.
To work around this behavior, either use a binary comparison collation, or replace the '-' s with '~'s.
For example,
Hope this helps.
hanigogo
Member
2 Points
1 Post
Re: Sorting Datatable With Hyphen / Dash vb.net
Jan 02, 2013 08:31 AM|LINK
hello, you can try it