CODEWITHSUNDEEP
sql-servercollationstring-comparison
user1365888
user1365888

Reputation: 3

Select statement returns nothing when column collation SQL_Latin1_General_CP1_CI_AS in T-sql

I have a select statement as below:

SELECT Veri from tblTest 
where  CAST(Veri COLLATE SQL_Latin1_General_CP1_CI_AS as varchar(10))=
 CAST('БHО' COLLATE SQL_Latin1_General_CP1_CI_AS as varchar(10))

Column Veri has collation of type SQL_Latin1_General_CP1_CI_AS. There is a row with Veri equals БHО. However, select statement returns nothing. Table tblTest's collation is also SQL_Latin1_General_CP1_CI_AS. What am I doing wrong?

Edit: Column definition for column Veri is as follow:

CONDENSED_TYPE: nvarchar(50) TABLE_SCHEMA: dbo TABLE_NAME: tblTest COLUMN_NAME: Veri ORDINAL_POSITION: 2 COLUMN_DEFAULT: NULL IS_NULLABLE: NO DATA_TYPE: nvarchar CHARACTER_MAXIMUM_LENGTH: 50 CHARACTER_OCTET_LENGTH: 100 NUMERIC_PRECISION:NULL NUMERIC_PRECISION_RADIX: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_CATALOG: NULL CHARACTER_SET_SCHEMA: NULL COLLATION_NAME: SQL_Latin1_General_CP1_CI_AS CHARACTER_SET_NAME: UNICODE COLLATION_CATALOG: NULL DOMAIN_SCHEMA: NULL DOMAIN_NAME: NULL

Upvotes: 0

Views: 1052

Answers (1)

Rhys Jones
Rhys Jones

Reputation: 5518

In T/SQL the string constant 'БHО' is an ANSI string, and 'Б' is not available so you'll get the question marks that @EduardUta queried. You need to work with Unicode strings, using the N prefix for string constants and nvarchar. Try this;

SELECT Veri from tblTest 
where  CAST(Veri COLLATE SQL_Latin1_General_CP1_CI_AS as nvarchar(10)) =
CAST(N'БHО' COLLATE SQL_Latin1_General_CP1_CI_AS as nvarchar(10))

You may be able to remove the COLLATE directives - depends on your schema.

Another thing you can do is to examine a string character by character to see what each character actually is. For example, in your string 'БHО' it might look like the Cyrillic capital letter Be followed by the English letters H and O, but it's not, that's why you are not getting a match.

declare @s nvarchar(100) = N'БНО'
declare @i int = 0

while (@i <= len(@s))
begin
    print substring(@s, @i, 1) + N' - 0x' + convert(varchar(8), convert(varbinary(4), unicode(substring(@s, @i, 1))), 2)
    set @i = @i + 1
end

Try typing the Н and О in the string N'БНО' above and running again - you'll see 0x48 and 0x4F respectively.

Hope this helps,

Rhys

Upvotes: 1

Related Questions