Posted by Erland Sommarskog on 06/29/05 00:37

Kavitha (kavithareddykr@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, esquel@sommarskog.se

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

[Back to original message]