Compare characters in WHERE clause using COLLATE clause

The type of COLLATE is important in compare values with low-hight letter, accent letter or diacritical letter.

First we compare character in CI and AI:

SELECT 'the same in CI_AI is a=ą' as if_a_ą
WHERE 'a' COLLATE Polish_CI_AI = 'ą' COLLATE Polish_CI_AI;

SELECT 'the same in CI_AI a=A' as if_a_A
WHERE 'a' COLLATE Polish_CI_AI = 'A' COLLATE Polish_CI_AI;

SELECT 'the same in CI_AI a=á' as if_a_á
WHERE 'a' COLLATE Polish_CI_AI = 'á' COLLATE Polish_CI_AI;

ff1

Next we compare character in CI and AS :

SELECT 'the same in CI_AS is a=ą' as if_a_ą
WHERE 'a' COLLATE Polish_CI_AS = 'ą' COLLATE Polish_CI_AS;

SELECT 'the same in CI_AS a=A' as if_a_A
WHERE 'a' COLLATE Polish_CI_AS = 'A' COLLATE Polish_CI_AS;

SELECT 'the same in CI_AS a=á' as if_a_á
WHERE 'a' COLLATE Polish_CI_AS = 'á' COLLATE Polish_CI_AS;

ff2

And next we compare character in CS and AS :

SELECT 'the same in CS_AS is a=ą' as if_a_ą
WHERE 'a' COLLATE Polish_CS_AS = 'ą' COLLATE Polish_CS_AS;

SELECT 'the same in CS_AS a=A' as if_a_A
WHERE 'a' COLLATE Polish_CS_AS = 'A' COLLATE Polish_CS_AS;

SELECT 'the same in CS_AS a=á' as if_a_á
WHERE 'a' COLLATE Polish_CS_AS = 'á' COLLATE Polish_CS_AS;

ff3

Next we compare character in CS and AI :

SELECT 'the same in CS_AI is a=ą' as if_a_ą
WHERE 'a' COLLATE Polish_CS_AI = 'ą' COLLATE Polish_CS_AI;

SELECT 'the same in CS_AI a=A' as if_a_A
WHERE 'a' COLLATE Polish_CS_AI = 'A' COLLATE Polish_CS_AI;

SELECT 'the same in CS_AI a=á' as if_a_á
WHERE 'a' COLLATE Polish_CS_AI = 'á' COLLATE Polish_CS_AI;

ff4

At the end we compare character in BIN :

SELECT 'the same in BIN is a=ą' as if_a_ą
WHERE 'a' COLLATE Polish_BIN = 'ą' COLLATE Polish_BIN;

SELECT 'the same in BIN a=A' as if_a_A
WHERE 'a' COLLATE Polish_BIN = 'A' COLLATE Polish_BIN;

SELECT 'the same in BIN a=á' as if_a_á
WHERE 'a' COLLATE Polish_BIN = 'á' COLLATE Polish_BIN;

ff3These examples show how result we may obtain using appropriate collation.

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.