Monday, March 12, 2018

SQL SERVER - How use COLLATE over special chars

Sometimes (dependent how is your DB created) you need specify character set for some command that used special text chars. Here, for example in WHERE section.
Without COLLATE..
select count( * ) as rows
from salesline a
where
a.itemid = 'PŘEPRAVNÉ' and
a.confirmeddlv >= '2018/02/01' and
a.confirmeddlv <= '2018/02/07'
Output:
rows
-----------
0

(1 row(s) affected)
..you get empty result set.

But with COLLATE..
select count( * ) as rows
from salesline a
where
a.itemid collate latin1_general_ci_ai = 'PŘEPRAVNÉ' and
a.confirmeddlv >= '2018/02/01' and
a.confirmeddlv <= '2018/02/07'
Output:
rows
-----------
4

(1 row(s) affected)
..it is something else.

NOTE: Collate DB parameter you can get from DB properties.

No comments:

Post a Comment