I sometimes have to compare character data with particular German collation support where umlauts like 'ä' are treated equally to their "expanded form" 'ae'. That can be done since SA 10.0.1 with collation tailoring, in particular with the SortType phonebook:

SELECT
   COMPARE( N'Schaefer-Maassen',N'Schäfer-Maaßen'),
   COMPARE( N'Schaefer-Maassen',N'Schäfer-Maaßen',
     'UCA(locale=de-de;accent=ignore;SortType=standard)'),
   COMPARE( N'Schaefer-Maassen',N'Schäfer-Maaßen',
     'UCA(locale=de-de;accent=ignore;SortType=phonebook)');
-- return -1, -1, 0

While this does work, it requires the usage of the compare() function. I have lately seen that MS SQL 2000ff. has a somewhat "smoother" syntax by allowing a "COLLATE clause", both when defining table columns and when doing comparions. So I could just write

SELECT * FROM sys.dummy
    WHERE N'Schaefer-Maassen' = N'Schäfer-Maaßen' COLLATE German_PhoneBook_CI_AI

I don't know whether a COLLATE clause is standard SQL (just because the MS SQL 2000 docs are very silent w.r.t to Standard SQL compliance). The difference between both syntax constructs seems quite small, however, with longer statements the MS SQL syntax seems less verbose. And it's easier to turn a "standard comparison" into a collation-based when one just has to append a COLLATE clause.

So my question is:

Can a collation-based comparison without the COMPARE function() be done within SA, too?

(I'm not refering to changing the default database collation, it's only for particular cases.)

asked 08 Feb '10, 13:14

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 08 Feb '10, 20:23

Needless to say, there are lots of SA 11.0.1 features I'm constantly missing while using MS SQL 2000:) - One of the worst is that one cannot refer to SELECT list aliases in the WHERE or GROUP BY clauses. That's really bad when dealing with complex expressions:(

(08 Feb '10, 13:33) Volker Barth

This is an answer by John Smirnios (Sybase) from the sybase.public.sqlanywhere.general NG where I have asked this question, too:

We do not support the COLLATION clause or per-column collations. Although we've talked about them, I know of no concrete plans to implement them.

BTW, it doesn't affect comparison predicates but there is a SORT_COLLATION option that you can specify that will automatically apply SORTKEY to the string values in an ORDER BY clause. It doesn't help your particular issue but I mention it just in case you have a similar issue related to sorting.

-john.

permanent link

answered 17 Feb '10, 09:09

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×17
×4

question asked: 08 Feb '10, 13:14

question was seen: 3,504 times

last updated: 17 Feb '10, 09:09