Re: Character expansion with ICU collations

On 09.06.21 17:31, Finnerty, Jim wrote:
> CREATE COLLATION CI_AS (provider = icu,
> locale=’utf8(at)colStrength=secondary’, deterministic = false);
>
> CREATE TABLE MyTable3
> (
>
>     ID INT IDENTITY(1, 1),
>     Comments VARCHAR(100)
>
> )
>
> INSERT INTO MyTable3 (Comments) VALUES ('strasse')
> INSERT INTO MyTable3 (Comments) VALUES ('straße')
> SELECT * FROM MyTable3 WHERE Comments COLLATE CI_AS = 'strasse'
> SELECT * FROM MyTable3 WHERE Comments COLLATE CI_AS = 'straße'
>
> We would like to control whether each SELECT statement finds both
> records (because the sort key of ‘ß’ equals the sort key of ‘ss’), or
> whether each SELECT statement finds just one record.

You can have these queries return both rows if you use an
accent-ignoring collation, like this example in the documentation:

CREATE COLLATION ignore_accents (provider = icu, locale =
'und-u-ks-level1-kc-true', deterministic = false);