> 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.

> BYTW, I found an very instereting stuff. Please the script below,
>
> The first 1 and 2 select statement should return same result, but they
> do not; and only difference between them is 1 statement does not have
> the M character inside the like statement.
>
>
> CREATE TABLE tbl
> (lastname nvarchar(30) NOT NULL)
> go
> INSERT tbl (lastname) VALUES ('MópeX')
> INSERT tbl (lastname) VALUES ('MópeY')
> INSERT tbl (lastname) VALUES ('MopeZ')
> go
> SELECT * FROM tbl WHERE lastname Collate
> SQL_Latin1_General_CP1_CI_AI LIKE N'%[óo]pe%'
> SELECT * FROM tbl WHERE lastname Collate
> SQL_Latin1_General_CP1_CI_AI LIKE N'%M[óo]pe%'
> SELECT * FROM tbl WHERE lastname Collate
> SQL_Latin1_General_CP1_CI_AI LIKE N'%M%[óo]pe%'
> SELECT * FROM tbl WHERE lastname Collate
> SQL_Latin1_General_CP1_CI_AI LIKE N'%[o]pe%'
> GO
>
> drop table tbl

Erland Sommarskog wrote:
> dBlue (zkvneml@hotmail.com) writes:
> > BYTW, I found an very instereting stuff. Please the script below,
> >
> > The first 1 and 2 select statement should return same result, but they
> > do not; and only difference between them is 1 statement does not have
> > the M character inside the like statement.
> >
> >
> > CREATE TABLE tbl
> > (lastname nvarchar(30) NOT NULL)
> > go
> > INSERT tbl (lastname) VALUES ('M=C3=B3peX')
> > INSERT tbl (lastname) VALUES ('M=F3peY')
> > INSERT tbl (lastname) VALUES ('MopeZ')
> > go
> > SELECT * FROM tbl WHERE lastname Collate
> > SQL_Latin1_General_CP1_CI_AI LIKE N'%[=C3=B3o]pe%'
> > SELECT * FROM tbl WHERE lastname Collate
> > SQL_Latin1_General_CP1_CI_AI LIKE N'%M[=C3=B3o]pe%'
> > SELECT * FROM tbl WHERE lastname Collate
> > SQL_Latin1_General_CP1_CI_AI LIKE N'%M%[=C3=B3o]pe%'
> > SELECT * FROM tbl WHERE lastname Collate
> > SQL_Latin1_General_CP1_CI_AI LIKE N'%[o]pe%'
> > GO
> >
> > drop table tbl
>
> But "M=C3=B3peX" is not the same as "M=F3peX". The first string has six
> characters, the second has five. Both strings match N'%[=C3=B3o]pe%' -
> that is first any number of characters and then exactly one of
> "=C3", "=B3" or "o" and then "pe" followed by any number of chars.
>
> On the other hand, the string N'%M[=C3=B3o]pe%', you say that there
> should be an M and then exactly one of "=C3", "=B3" or "o" and then "pe",
> which there is in the six-char string.
>
> OK, so that "M=C3=B2peZ" is really a representation of "M=F3peX", to whit
> the bit pattern that you have in UTF-8, but stored in a string which
> is supposed to hold UTF_16 values, and then it goes downhill from
> there.
>
> So I guess you really problem is why you have UTF-8 encodings in the
> wrong place. When I saw your first posting, I assumed that the mangled
> UTF-8 came from the news posting itself, but it appears now that it
> comes from the database.
>
> --
> 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