MS SQL Server MSEQ - N prefex in a non-unicode database

Author

N prefex in a non-unicode database

kiran_yalla

2005-07-15, 9:23 am

This problem is about N prefex in a non-unicode database.
Collation name of the database is " SQL_Latin1_General_C
P1_CL_AS".

Though the database is a non-unicode database, our application generates
N prefexed (before the string constants) queries. This is because, our
assumption is that non-unicode databse in sql2000 works fine with regular
string constants and N prefixed string constants.

The query is as follows.
SELECT iG_Category_Ita FROM Company WHERE iG_Category_Ita NOT LIKE N'%'

Here iG_Category_Ita is of type varchar(50) and it can take NULL values.

The problem here is, the out put we are getting when the string is not N
prefexed
is diff from the query with N prefexed.

In other words,
1. Output of SELECT iG_Category_Ita FROM Company WHERE iG_Category_Ita
NOT LIKE N'%'
is diff from
2. Output of SELECT iG_Category_Ita FROM Company WHERE iG_Category_Ita
NOT LIKE '%'

second query is returning table rows whose iG_Category_Ita = NULL but first
query is not returning NULL rows.

Can someone please help me if our assumption is correct or not?

Thanks & Regards,
Kiran

Michael Cheng [MSFT]

2005-07-18, 3:23 am

Hi Kiran,

Welcome to use MSDN Managed Newsgroup!

'N' is used to represent Unicode character so when you use N'%' it
indicates that the potential data types that you are using is either
nvarchar or nchar (both are used to support unicode character sets) where
are when we use '%' the data types would be char or varchar.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

kiran_yalla

2005-07-18, 3:23 am

Thanks Michael for the info.

My doubt here is, can we use N’%’ for varchar/char types? We generalized our
application earlier to generate N prefixed string constants irrespective of
whether the type is nvarchar/nchar or varchar/char. I would like to know if
this generalization is valid or not.

Regards,
Kiran

"Michael Cheng [MSFT]" wrote:

> Hi Kiran,
>
> Welcome to use MSDN Managed Newsgroup!
>
> 'N' is used to represent Unicode character so when you use N'%' it
> indicates that the potential data types that you are using is either
> nvarchar or nchar (both are used to support unicode character sets) where
> are when we use '%' the data types would be char or varchar.
>
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================
====================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

kiran_yalla

2005-07-18, 3:23 am

Michael,

I would like to add one more point here. This generalization works fine in
all other cases. We are facing problem only in this particular case. I would
also like to know if Microsoft is aware of this situation/problem.

Regards,
Kiran

"kiran_yalla" wrote:
[color=darkred]
> Thanks Michael for the info.
>
> My doubt here is, can we use N’%’ for varchar/char types? We generalized our
> application earlier to generate N prefixed string constants irrespective of
> whether the type is nvarchar/nchar or varchar/char. I would like to know if
> this generalization is valid or not.
>
> Regards,
> Kiran
>
>
> "Michael Cheng [MSFT]" wrote:
>

Michael Cheng [MSFT]

2005-07-19, 7:24 am

Hi Kiran,

Thanks for your update.

- My doubt here is, can we use N'' for varchar/char types?
No, it is not recommanded to do so. But if your database is an English one
and it works for most times, it think it's OK if you leave them what they
used to be.

Yes, I have realized SQL cannot handle the NULL values after convert() but
not sure why. I will let our development guys know about this.

Thanks for your patience and cooperation.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Sponsored Links