Unicode Comparison - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unicode Comparison

Author

Topic

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-30 : 13:24:21

I ran into an issue the other day and I was wondering if anyone know another way to get around it. Basically, when comparing a string with a Unicode character to another string without said character they are reported to be equal. The only way I know how to get the correct comparison is to cast each string to VARBINARY. Maybe this is a special issue because of the character in question (Unicode 8238) which causes mirroring? Here is some code to show what I am talking about:

DECLARE @S1 NVARCHAR(10)
DECLARE @S2 NVARCHAR(10)

SET @S1 = N'wedding ' + NCHAR(8238)
SET @S2 = N'wedding'

SELECT
	CASE
		WHEN @S1 = @S2
			THEN 1
		ELSE 0
	END,
	CASE
		WHEN @S1 COLLATE SQL_Latin1_General_CP1_CI_AS = @S2 COLLATE SQL_Latin1_General_CP1_CI_AS
			THEN 1
		ELSE 0
	END,
	CASE
		WHEN CAST(@S1 AS VARBINARY) = CAST(@S2 AS VARBINARY)
			THEN 1
		ELSE 0
	END

Anyone know another way to do this comparison correctly?

Also for those that have not seen it, try running this code then past the character into management studio at the begining of a like with text on it.

SELECT NCHAR(8238)

Thanks,

-Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 13:51:30

[code]DECLARE @S1 NVARCHAR(10)
DECLARE @S2 NVARCHAR(10)

SET @S1 = N'wedding ' + NCHAR(8238)
SET @S2 = N'wedding'

SELECT
CASE
WHEN @S1 = @S2
THEN 1
ELSE 0
END,
CASE
WHEN @S1 COLLATE SQL_Latin1_General_CP1_CI_AS = @S2 COLLATE SQL_Latin1_General_CP1_CI_AS
THEN 1
ELSE 0
END,
CASE
WHEN CAST(@S1 AS VARBINARY) = CAST(@S2 AS VARBINARY)
THEN 1
ELSE 0
END,
CASE
WHEN @S1 LIKE @S2
THEN 1
ELSE 0
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-30 : 14:02:26

Thanks Peso,

I guess I'm trying to better understand why a direct comparison does not work? I must be missing something, but I don't see why an equality comparision would fail but a like comparision would succeed. If anyone can shed some light I'd appreciate it.

-Ryan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-30 : 14:04:14

I think I figured it out..? It appears that SQL deosn't know how to mapp the unicode character sp it treats it like a trailing space.

EDIT: Clarification.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources