|
|
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 "Lpez" 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 'Lp%'", the row of Lpez
> 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 ('Mpez')
INSERT tbl (lastname) VALUES ('lopez de la serra')
INSERT tbl (lastname) VALUES ('x lpez')
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
[Back to original message]
|