December 7, 2015 at 1:14 am
Hello.
I want to ask about the possibility of solving the problem below. This problem is SQL2008 and SQL2012.
Database / table is always Collation: SQL_Czech_CP1250_CI_AS
It is a combination of 'CI ...', '... C', 'CI ...' at the beginning of the string
Example :
DECLARE @TestTable Table --Or CREATE TABLE.....
(
KOD VARCHAR(20) COLLATE SQL_Czech_Cp1250_CI_AS
)
INSERT @TESTTABLE
(
KOD
)
VALUES ('CINTERIOUS'),
('cinterious'),
('Cinterious'),
('cInterious'),
('COKOLIV'),
('cokoliv'),
('Cokoliv'),
('cOkoliv')
SELECT * FROM @TESTTABLE WHERE KOD LIKE 'ci%' --OK
SELECT * FROM @TESTTABLE WHERE KOD LIKE 'CI%' --ERROR : empty resultset - nothing is returned
SELECT * FROM @TESTTABLE WHERE KOD LIKE '%CI%' --OK
SELECT * FROM @TESTTABLE WHERE KOD LIKE 'co%' --OK
SELECT * FROM @TESTTABLE WHERE KOD LIKE 'CO%' --OK
Thank
December 7, 2015 at 3:01 am
BrainDonor (12/7/2015)
I suspect that using the Czech character set requires that you use Unicode - NVarChar works fine, as does (on my machine)
SELECT * FROM @TESTTABLE WHERE KOD LIKE 'CI%' COLLATE Latin1_General_CI_ASwhich also points to a collation issue.
I don't think that this is the case. I admit that I'm not an expert on the subject, and that I haven't worked with code pages that support a foreign language for a long time, but as far as I know, each code page is using a different ASCII table, but all of them differ in high part of the ASCII table. The low part that controls among other things the English alphabet is the same. Since in this example the search is done for English letters only, I don't think that Unicode should have been used. Another point is that this is why code page exist. If someone wants to support only English + one foreign language without using Unicode, he can use a specific code page and it should work. Having said all of that, I have to admit that I have no idea why this example doesn't work.
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 7, 2015 at 3:01 am
Yes, if I change the type of VARCHAR to NVARCHAR then that's fine. But the data model contains many tables with type VARCHAR and many objects working with VARCHAR.
I know that SQL_Czech_CP1250_CI_AS is outdated collation. It should be used collation Czech_CI_AS. Changing the type or collation is not easy. I was just looking for a simpler solution ... I thought Microsoft knows about it and will issue a patch.:-)
December 7, 2015 at 4:46 am
When I use Czech_CI_AS then all OK
December 7, 2015 at 3:25 pm
SELECT * FROM @TESTTABLE WHERE KOD LIKE N'CI%'
All good. 4 records returned:
KOD
CINTERIOUS
cinterious
Cinterious
cInterious
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply