What does N' stands for in a SQL script ? (the one used before characters in insert script)

Question

I generated an sql script like this,

INSERT [dbo].[TableName] ([Sno], [Name], [EmployeeId], [ProjectId], [Experience]) 
VALUES (1, N'Dave', N'ESD157', N'FD080', 7)

I wonder whats that N' exactly mean and whats its purpose here.

NOTE: By searching for the answer all i can get is that N' is a prefix for National language standard and its for using unicode data. But honestly i am not able to get a clear idea about the exact operation of N' here. I'd appreciate your help and please make it in more of an understandable way. Thanks in advance.

Other answers seem to already cover it, but just in case you want it, here's a like to the [documentation](http://msdn.microsoft.com/en-us/library/ms179899.aspx) if you want to read more. (I assume you're using SQL Server) — Damien_The_Unbeliever, Jan 16 '13 at 07:30
Possible duplicate of [What is the meaning of the prefix N in T-SQL statements?](https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements) — KyleMit, Aug 14 '18 at 22:10

score 35 · Accepted Answer · edited Aug 14 '18 at 23:42

35

N is used to specify a unicode string.

Here's a good discussion: Why do some SQL strings have an 'N' prefix?

In your example N prefix is not required because ASCII characters (with value less than 128) map directly to unicode. However, if you wanted to insert a name that was not ASCII then the N prefix would be required.

INSERT [dbo].[TableName] ([Sno], [Name], [EmployeeId], [ProjectId], [Experience]) 
VALUES (1, N'Wāhi', 'ESD157', 'FD080', 7)

edited Aug 14 '18 at 23:42

KyleMit

30,350
66
462
664

answered Jan 16 '13 at 07:28

Richard Schneider

34,944
9
57
73

i just tried out the example input as you described. It works!! Thanks for making me understand the difference and just out of curiosity may i know in what case input like this would be required , i mean in real time requirement.. – Ebenezar John Paul Jan 16 '13 at 09:35
In the rest of world (outside of USA and UK) names can contain more than just the A-Z characters. As in my example Wāhi is a Maori name. Look at the macron over the 'a'. – Richard Schneider Jan 16 '13 at 09:40
1

ya i noticed. When i tried it with prefix N' in my insert command it got inserted as it is (Wāhi). But when i tried the same without the N' only Wahi got inserted. And that's how i understood it. – Ebenezar John Paul Jan 16 '13 at 09:53

score 13 · Answer 2 · edited Aug 14 '18 at 23:42

13

The "N" prefix stands for National Language in the SQL-92 standard, and is used for representing unicode characters.

Any time you pass Unicode data to SQL Server you must prefix the Unicode string with N.

It is used when the type is from NVARCHAR, NCHAR or NTEXT.

For more info refer to this: Why do some SQL strings have an 'N' prefix?

edited Aug 14 '18 at 23:42

KyleMit

30,350
66
462
664

answered Jan 16 '13 at 07:27

Vishal Suthar

17,013
3
59
105

score 6 · Answer 3 · answered Jan 16 '13 at 07:30

6

'abcd' is a literal for a [var]char string (or maybe text, but varchar(max) would be more common now) - occupying 4 bytes memory, and using whatever code-page the SQL server is configured for. N'abcd' is a literal for a n[var]char string (or maybe ntext, but nvarchar(max) would be preferable), occupying 8 bytes of memory using UTF-16. This allows for full international usage, and frankly n[var]char should probably be the default in most systems.

answered Jan 16 '13 at 07:30

Marc Gravell

1,026,079
266
2,566
2,900

As literals, they will never be interpreted as `text` or `ntext`. They may be used to populate a variable or column of such types, but that's technically a separate conversion. They're documented as producing the `(max)` variants. – Damien_The_Unbeliever Jan 16 '13 at 07:36
@Damien_The_Unbeliever "... is a literal for a ..." – Marc Gravell Jan 16 '13 at 07:40

score 2 · Answer 4 · answered Jan 16 '13 at 07:28

2

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.

SOURCE

answered Jan 16 '13 at 07:28

John Woo

258,903
69
498
492

thak you very much for responding. – Ebenezar John Paul Jan 16 '13 at 09:39

Habib · Answer 5 · 2013-01-16T07:35:30.363

1

N is to specify that its a string type value.

[N]'tsql_string'

Is a constant string. tsql_string can be any nvarchar or varchar data type. If the N is included, the string is interpreted as nvarchar data type.

edited Jan 16 '13 at 07:35

answered Jan 16 '13 at 07:26

Habib

219,104
29
407
436

The type could also be NTEXT. – DocMax Jan 16 '13 at 07:28
@DocMax - Actually, it would be `nvarchar(max)` if it's more than 8000 bytes. – Damien_The_Unbeliever Jan 16 '13 at 07:32
use for string literals which indicate treat text as unicode. – Pranay Rana Jan 16 '13 at 07:33

MikeT · Answer 6 · 2013-12-02T10:37:33.287

each country has its own specific letters and symbols so a database set up for English US will not recognise the £ symbol which a English UK database would, the same goes for Spanish, French, German

Also other languages like Chinese, Japanese, Hebrew, Arabic don't use any Latin characters.

so anyone trying to enter any data not contained in the local character set will fail or suffer data corruption, if you are using varchar, so if there is even the remotest possibility that your database will need to support more than one local character set then you have to use the nationalised language character set aka unicode aka NChar, which allows the character sets nationality to be recorded with the character. providing international text support

Likewise adding the N Prefix to a string instructs the database to include the Nation code as well as the character code

as a side note, i've heard it argued that because of the relative cheapness of storage these days there is no longer any justification for not using NChar and NVarchar, I'm not sure i agree with them but i don't disagree either — MikeT, Dec 02 '13 at 10:24

score -2 · Answer 7 · edited Oct 20 '20 at 02:06

DECLARE

   TYPE name_salary_rt IS RECORD (

      table_names     VARCHAR2 (1000),

      counts  NUMBER

   );

VSQL varchar2(2000);

   TYPE name_salary_aat IS TABLE OF name_salary_rt

      INDEX BY PLS_INTEGER;


   l_employees   name_salary_aat;

BEGIN

   EXECUTE IMMEDIATE

      q'[select table_name ,count(*) CountF

           from all_tab_columns  where rownum<100

         group by table_name]'

      BULK COLLECT INTO l_employees;


   FOR indx IN 1 .. l_employees.COUNT

   LOOP

VSQL:=VSQL||'  select '''||l_employees (indx).table_names||''','''|| l_employees (indx).counts ||''' from dual ';

if indx<l_employees.COUNT then

VSQL:=VSQL|| ' union all ';

else

VSQL:=VSQL||';';

end if;


  --    DBMS_OUTPUT.put_line (l_employees (indx).table_names||','|| l_employees (indx).counts);

    --    DBMS_OUTPUT.put_line (l_employees (indx).countf);

   END LOOP;

DBMS_OUTPUT.put_line (VSQL);

-- execute immediate  VSQL; 

END;

please add a note to indicate what you are trying to explain — nahidf, Oct 19 '20 at 16:48
In addition to @nahidf's feedback, this post also has some formatting issues. Please place the entirety of the content in a code block. — Jeremy Caney, Oct 19 '20 at 19:11

What does N' stands for in a SQL script ? (the one used before characters in insert script)

7 Answers7

Linked

Related