Central european characters in SQL

Question

I have an issue. I have data stored on SQL server with central european characters like "č", "ř", "ž" etc. On the database I have the "Czech_CI_AS" collation which should accepted these characters. But when I try to select for example name of the street with this characters like this:

SELECT *
FROM Street where Name = 'Čáslavská'

It returns me nothing When I remove the "č" it returns me what I need.

SELECT *
FROM Street where Name like '%áslavská'

I have this column in nvarchar type. But I cannot use the N character before my string because the external applications use this table for read and selects are made automaticlly.

Is here any solution? Or have I got something wrong?

Thanks for any help

Answer 1

@YuriyTsarkov really deservers the credit here. To elaborate on his answer.

From MSDN:

Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

Example

-- Storing Čáslavská in two vars, with and without N prefix.
DECLARE @Test_001 NVARCHAR(255)  =  'Čáslavská' COLLATE Czech_CI_AS;
DECLARE @Test_002 NVARCHAR(255)  = N'Čáslavská' COLLATE Czech_CI_AS;

-- Test output.
SELECT 
    @Test_001   AS T1,
    @Test_002   AS T2
;

Returns

T1         T2
Cáslavská  Čáslavská

Answer 2

You need to update all your external applications code to use selects with N, or, you need to change collation of your column to same, as used by external applications. It may cause some data loss.