on 2010 Feb 08 1:14 PM
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.)
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
76 | |
30 | |
8 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.