Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mtl777
Yak Posting Veteran
63 Posts |
 Posted - 2012-03-18 : 00:33:46
|
| The default collation in my SQL Server installation is SQL_Latin1_General_CP1_CI_AS. I would like it such that a tilde symbol (~) would be considered as "greater" than the letter Z. Currently, if I have "Zebra" and "~Alpha" in the Description field, the sort order will be:~AlphaZebraI would like the sort order to be:Zebra~AlphaWhat collation should I use to achieve this? Note: I still want it to be case insensitive and the only thing I want it to do is to treat the tilde (~) character as greater than the letter Z. I have tried the BIN option and it works but it's no longer case insensitive.Your help would be greatly appreciated. Thanks in advance! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-03-18 : 01:07:27
|
[code]ORDER BY case when left(col, 1) = '~' then 2 else 1 end, col[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2012-03-19 : 14:39:17
|
| Thanks khtan. While waiting for replies to this thread, I ended up doing this:SELECT * FROM MyTableORDER BY Upper(Description) COLLATE Modern_Spanish_BINModern_Spanish_BIN achieves the desired collating sequence but makes it case sensitive. Using the Upper function forces it to be case insensitive. It's good enough and it works with characters other than tilde, but I wish there was a collation that could do what I wanted without the need for such tweaks. |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2012-03-20 : 09:38:48
|
Running the code generated by the following suggests collation does not work the way you want.SELECT 'IF N''~'' COLLATE ' + [name] + ' > N''Z'' COLLATE ' + [name] + ' SELECT ''' + [name] + ''';'FROM ::fn_helpcollations()WHERE [name] LIKE '%CI%'; |
|
|
|
|
|
|
Help with collation