how to insert double byte to DB without add N into the literal

Question

a lot of people say you can do this:

declare @str        nvarchar(255);
declare @finalValue nvarchar(255);

set @str        = N'好使';
set @finalValue = @str;

But this is not my case. What I want to do is something like this:

  declare @str        nvarchar(255);
  declare @finalValue nvarchar(255);

  set @str        = '好使'     ;
  set @finalValue = 'N'+ @str ;

  print @finalValue;

because the value @str actually is from somewhere else(CSV).

In a stored procedure, I got the @str value from CSV and it is double byte (Chinese/Japanese), which is saved correctly) and I want to add N before inserting it in the DB table (without the N, it displays as ?? in the table.)

How can I add the N?

Answer 1

OK.

The N you're talking about is not a function that does anthing. It's a part of the syntax of SQL that defines string literals.

Single-byte string literals

A single-byte string literal consists of a lead-in single quote (') , followed by zero or more other characters other than a single quote, followed by a lead-out single quote ('). To remove ambiguity, any embedded single quotes are escaped by doubling them. The lexical value of a string literal does not include the lead-in/lead-out quotes nor the doubled internal escapes, so the string literal

'That''s crazy!`

has the lexical value

That's crazy!

Such a string literal is defined as a single-byte string as interpreted by the default collation of the database. Further, each character occupies a single byte.

Consequently, an expression such as

select *
from foo
where some_column = 'That''s crazy!'

is exactly the same as

declare @temp char(13) = 'That''s crazy!'

select *
from foo
where some_column = @temp

Notes: while you can put unicode characters, such as Katagana or Hiragana in a single-byte string literal, you're unlikely to get what you expect.

Double-Byte (Unicode) String Literals

Double-byte (Unicode) string literals are defined in the same way, except that the literal is prefixed with N to indicate that it's special:

`N'That''s crazy!'

again has the lexical value

That's crazy!

but in this case, each character occupies two bytes and is Unicode UCS-2 encoded.

And again, an expression such as

select *
from foo
where some_column = N'That''s crazy!'

is exactly the same as

declare @temp nchar(13) = 'That''s crazy!'

select *
from foo
where some_column = @temp

So...your question "How can I add the N?" makes no sense. The N isn't a function that changes anything. You can't apply it to a variable (you already did in declare the variable to be of type nchar or nvarchar.

You problem lies in how you are reading your CSV file and how you're getting the data into your @str variable.