| Author |
Unicode chars with like query statement problem
|
|
| Kavitha 2005-06-28, 11:23 am |
| 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 "=C3=B3pez" in them. But the second query returns only records
with "lopez" in the lastname and not records that contain "l=C3=B3pez".
Can anyone please tell me what is it that i am missing?
SELECT *
FROM tbUsers
WHERE (LastName LIKE '%[o=C3=B3]pez%')
SELECT *
FROM tbUsers
WHERE (LastName like '%l[o=C3=B3]pez%')
thanks
| |
| Erland Sommarskog 2005-06-28, 8:23 pm |
| Kavitha (kavithareddykr@gmai
l.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髉ez')
INSERT tbl (lastname) VALUES ('lopez de la serra')
INSERT tbl (lastname) VALUES ('x l髉ez')
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/techin.../2000/books.asp
| |
| Kavitha 2005-06-29, 9:23 am |
| 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 "=C3=B3" 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=C3=B3pez')
SELECT * FROM tbl WHERE lastname LIKE N'%& #91;o=F3=C3=B3]pez%'
SELECT * FROM tbl WHERE lastname LIKE N'%l& #91;o=F3=C3=B3]pez%'
| |
| hwoess 2005-06-29, 9:23 am |
| 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 2005-06-29, 8:24 pm |
| Kavitha (kavithareddykr@gmai
l.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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
|
|
|
|