Unicode chars with like query statement problem

[Kavitha]

I have a Users table which contains a nvarchar column LastName. The
first query below returns me all the records with LastName containing
"opez" or "ópez" in them. But the second query returns only records
with "lopez" in the lastname and not records that contain "lópez".
Can anyone please tell me what is it that i am missing?

SELECT *
FROM tbUsers
WHERE (LastName LIKE '%[oó]pez%')

SELECT *
FROM tbUsers
WHERE (LastName like '%l[oó]pez%')

thanks

[Erland Sommarskog]

Kavitha (ka************@gmail.com) writes:

I have a Users table which contains a nvarchar column LastName. The
first query below returns me all the records with LastName containing
"opez" or "ópez" in them. But the second query returns only records
with "lopez" in the lastname and not records that contain "lópez".
Can anyone please tell me what is it that i am missing?

SELECT *
FROM tbUsers
WHERE (LastName LIKE '%[oó]pez%')

SELECT *
FROM tbUsers
WHERE (LastName like '%l[oó]pez%')

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, es****@sommarskog.se

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

[Kavitha]

Thanks for the response. Here is some more information. I am using
utf-16 encoding and so when i store a "latin small letter o with
acute", it gets stored as "ó" which looks like a capital letter A
with tilde and a superscript 3.

After inserting the following row into the tbl Table, the first select
statement below works while the second one does not.

INSERT tbl (lastname) VALUES ('lópez')

SELECT * FROM tbl WHERE lastname LIKE N'%[oóó]pez%'

SELECT * FROM tbl WHERE lastname LIKE N'%l[oóó]pez%'

[hwoess]

Am 28 Jun 2005 08:22:51 -0700 schrieb Kavitha:

I have a Users table which contains a nvarchar column LastName. The
first query below returns me all the records with LastName containing
"opez" or "ópez" in them. But the second query returns only records
with "lopez" in the lastname and not records that contain "lópez".
Can anyone please tell me what is it that i am missing?

SELECT *
FROM tbUsers
WHERE (LastName LIKE '%[oó]pez%')

SELECT *
FROM tbUsers
WHERE (LastName like '%l[oó]pez%')

thanks

try this:
SELECT * FROM tbUsers
WHERE (LastName like N'%l[oó]pez%')

I think it needs the "N" in front of the string.

bye,
Helmut

[Erland Sommarskog]

Kavitha (ka************@gmail.com) writes:

Thanks for the response. Here is some more information. I am using
utf-16 encoding and so when i store a "latin small letter o with
acute", it gets stored as "ó" which looks like a capital letter A
with tilde and a superscript 3.

Apparently, you are using UTF-8 somewhere, and you end up with UTF-8
in a database that works with UTF-16.

Also see my reply to dBlue. (And if you are the same person, please
stick to one alias, and one thread.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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