Re: Character expansion with ICU collations

On 09.06.21 17:31, Finnerty, Jim wrote:
> CREATE COLLATION CI_AS (provider = icu, 
> locale=’utf8@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);