MS SQL Server - Sort sequence in SqlServer2005

Author

Sort sequence in SqlServer2005

2006-03-30, 9:23 am

I have a file that has a record in it with the ID [All] (including the
"[" and "]" characters). When I do a sort on the file, this record comes
out before the other alphanumeric IDs, even though the "[" character is
after the letter "Z" in the ASCII chart (char(91) for "[" vs char(90)
for "Z"). Also upper and lower case IDs are mixed together rather than
separated by case.

Looking through some documentation, I see there is a collating property
which must be set. However, all the documentation indicates that this
must be done during Setup. Is there a way to change the collating
sequence even after setup via SqlServer Management Studio? Also, what is
the correct collation choice I should use to get the ASCII sequencing?

Thanks

John Bell

2006-03-30, 11:23 am

Hi

You can use the collate keyword in your select statement to force a
collation, although if your default collation is wrong then you should change
it. e.g http://tinyurl.com/n9jna

John

"SH" wrote:

> I have a file that has a record in it with the ID [All] (including the
> "[" and "]" characters). When I do a sort on the file, this record comes
> out before the other alphanumeric IDs, even though the "[" character is
> after the letter "Z" in the ASCII chart (char(91) for "[" vs char(90)
> for "Z"). Also upper and lower case IDs are mixed together rather than
> separated by case.
>
> Looking through some documentation, I see there is a collating property
> which must be set. However, all the documentation indicates that this
> must be done during Setup. Is there a way to change the collating
> sequence even after setup via SqlServer Management Studio? Also, what is
> the correct collation choice I should use to get the ASCII sequencing?
>
> Thanks
>

2006-03-30, 8:23 pm

Thanks, John.

I'm wondering if there is a way to set the whole SqlServer (or at least
the whole database) to the proper collating sequence without having to
use the COLLATE keyword whenever I run into this problem.

John Bell wrote:[color=darkred
]
> Hi
>
> You can use the collate keyword in your select statement to force a
> collation, although if your default collation is wrong then you should change
> it. e.g http://tinyurl.com/n9jna
>
> John
>
>
> "SH" wrote:
>
>

David Portas

2006-03-30, 8:23 pm

SH wrote:
> Thanks, John.
>
> I'm wondering if there is a way to set the whole SqlServer (or at least
> the whole database) to the proper collating sequence without having to
> use the COLLATE keyword whenever I run into this problem.
>

You can set a default for the database (ALTER DATABASE) but because the
collation is set on each column anyway that doesn't help if you've
already created the tables. Also consider that a BIN (binary) collation
is case-sensitive, which is inconvenient in many cases. Changing the
database collation from case-insensitive to case-sensitive will break
any code that doesn't use the proper case to reference objects.

In your case I would just order by another column or use CASE
expressions to fix the order.

....
ORDER BY
CASE WHEN x = '[all]' THEN 1 ELSE NULL END, x

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

John Bell

2006-03-31, 3:23 am

Hi

It sounds like you need to change the default at all levels rather than just
the columns. In which case you will need to change it a column, database and
system level. There are many posts that already describe how to do this.

If you only specify it database level or for specific columns you may need
to make sure that you force the collation on temporary tables that are used
with these columns otherwise there could be collation conflicts.

John

"SH" wrote:

> Thanks, John.
>
> I'm wondering if there is a way to set the whole SqlServer (or at least
> the whole database) to the proper collating sequence without having to
> use the COLLATE keyword whenever I run into this problem.
>
> John Bell wrote:
>

Sponsored Links