Can I use collation-tailoring without the COMPARE (or SORTKEY) function?

[VolkerBarth]

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.)

[VolkerBarth]

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:(

[VolkerBarth · Accepted solution]

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.