Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this

SELECT * FROM a_table WHERE a_field LIKE '%а_pattern%'

until I change pattern to N'%а_pattern%'. I never had to add this prefix in the past, so I am curious. a_field is defined as nvarchar(255), but I think the reason is something else.

Thank you!

share|improve this question

migrated from stackoverflow.com Feb 8 '12 at 16:34

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 24 down vote accepted

The following articles have some good information on the question. The short answer is just that there's a type mismatch between the unicode column and non-unicode string literal you're using. From the KB article, it looks like omitting the N prefix might still work in some cases, but it would depend on the code page and collation settings of the database. That might explain the change in behavior, if you were previously having success with the no-prefix method.

http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html http://support.microsoft.com/default.aspx/kb/239530

share|improve this answer

This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT. See Article #2354 for a comparison of these data types.

Unicode is typically used in database applications which are designed to facilitate code pages which extend beyond the English and Western Europe code pages for example Chinese. Unicode is designed so that extended character sets can still "fit" into database columns.

share|improve this answer

I think the N-prefix tells sql server to treat the string as a unicode value. nvarchar columns are unicode so you should use this syntax when accessing these columns.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.