|
Home > Archive > MS SQL Server > June 2005 > Need Help on Query against the UTF-16 encoding character
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Need Help on Query against the UTF-16 encoding character
|
|
|
| Hi all,
Our webpage use the UTF-16 encoding, so the word "López" is saved in the
field as "López"; I need help to make the sql query with the condition of
"like 'Lo%'" to return me the row with "López". Any idea?
--
Thank You
Albion(052X)
| |
| John Bell 2005-06-29, 3:23 am |
| Hi
You don't post DDL, but I would expect you to be using Nvarchar columns and
prefixing constants with a leading N.
John
"dBlue" wrote:
> Hi all,
>
> Our webpage use the UTF-16 encoding, so the word "López" is saved in the
> field as "López"; I need help to make the sql query with the condition of
> "like 'Lo%'" to return me the row with "López". Any idea?
>
> --
> Thank You
>
> Albion(052X)
| |
|
| Hi,
Please try the script below. It is a bit weired to me.
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_C
P1_CI_AI LIKE N'%[óo]pe%'
SELECT * FROM tbl WHERE lastname Collate
SQL_Latin1_General_C
P1_CI_AI LIKE N'%M[óo]pe%'
SELECT * FROM tbl WHERE lastname Collate
SQL_Latin1_General_C
P1_CI_AI LIKE N'%M%[óo]pe%'
GO
Drop table tbl
The frist two select statement does not return same result as they
suppose to. Any idea?
BTW, please reply in other thread in the group of
comp.databases.ms-sqlserver in Google groups.
--
Thank You
Albion(052X)
"John Bell" wrote:
[color=darkred]
> Hi
>
> You don't post DDL, but I would expect you to be using Nvarchar columns and
> prefixing constants with a leading N.
>
> John
>
> "dBlue" wrote:
>
| |
| Sylvain Lafontaine 2005-06-29, 11:23 am |
| "López" is stored as "López" in the database, not as "López". However,
many SQL clients are unable to display Unicode characters and will display
"López" instead but that doesn't mean that you can use "López" for
comparing or storing "López" in the database.
Try displaying the length of the field in the database and you will see that
"López" is five letters, not six and that "López" is six letters, not five.
If your webpage is storing "López" instead of "López" in the database, then
you should corrects it, not trying to make comparaison between "López" and
"López".
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"dBlue" <dBlue@discussions.microsoft.com> wrote in message
news:27F1E61D-2061-4F2C-A9D3- 9DB664475221@microso
ft.com...
> Hi all,
>
> Our webpage use the UTF-16 encoding, so the word "López" is saved in the
> field as "López"; I need help to make the sql query with the condition of
> "like 'Lo%'" to return me the row with "López". Any idea?
>
> --
> Thank You
>
> Albion(052X)
| |
| John Bell 2005-06-30, 3:23 am |
| Hi
You do not need the collate clause in the statement.
You may be wanting:
SELECT * FROM tbl WHERE lastname LIKE N'M[Ão]%pe%'
as Sylvian points out:
SELECT lastname, datalength(lastname)
FROM tbl
/*
lastname
------------------------------ -----------
MópeY 10
MopeZ 10
MópeX 12
*/
John
"dBlue" wrote:
[color=darkred]
> Hi,
>
>
> Please try the script below. It is a bit weired to me.
>
>
> 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_C
P1_CI_AI LIKE N'%[óo]pe%'
> SELECT * FROM tbl WHERE lastname Collate
> SQL_Latin1_General_C
P1_CI_AI LIKE N'%M[óo]pe%'
> SELECT * FROM tbl WHERE lastname Collate
> SQL_Latin1_General_C
P1_CI_AI LIKE N'%M%[óo]pe%'
> GO
>
>
> Drop table tbl
>
>
> The frist two select statement does not return same result as they
> suppose to. Any idea?
>
> BTW, please reply in other thread in the group of
> comp.databases.ms-sqlserver in Google groups.
>
> --
> Thank You
>
> Albion(052X)
>
>
> "John Bell" wrote:
>
|
|
|
|
|