Posted by Erland Sommarskog on 06/29/05 14:13

[posted and mailed, please reply in news]

dBlue (zkvneml@hotmail.com) writes:
> I have an issue on querying against UTF-16 encoded characters in
> SQL2000 database: For example the "L�pez" is saved into database as
> "López" (due to the UTF-16 encoding); somehow, when I query data
> with conditions of "like 'lop%'" or "like 'L�p%'", the row of L�pez
> would not return.
>
> NOTE: the accent insensitive collation can not help in this case.

I answered a very similar question yesterday, and while the username
and e-mail address, I would assume that you are the same person. I cannot
do much more but repeat my answer from yesterday:

I was not able to repeat this. If you can produce a script similar
to the one below that demonstrates the problem, it's a little easier
to get an idea of what is going on. Don't forget to include the collation
of the column.

CREATE TABLE tbl
(lastname nvarchar(30) COLLATE Finnish_Swedish_BIN NOT NULL)
go
INSERT tbl (lastname) VALUES ('Mopez')
INSERT tbl (lastname) VALUES ('M�pez')
INSERT tbl (lastname) VALUES ('lopez de la serra')
INSERT tbl (lastname) VALUES ('x l�pez')
go
SELECT * FROM tbl WHERE lastname LIKE '%[o�]pez%'
SELECT * FROM tbl WHERE lastname LIKE '%l[o�]pez%'
SELECT * FROM tbl WHERE lastname LIKE N'%[o�]pez%'
SELECT * FROM tbl WHERE lastname LIKE N'%l[o�]pez%'
go
drop table tbl


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Next in forum: Re: Unicode chars with like query statement problem
• Prev in forum: Re: Advance SQL question
• Thread view: Re: SQL 2000 and UTF-16 encoding

[Reply to this message]