0

I have [Data Set] table with columns called Id and IncrementalQuery.

In the IncrementalQuery column, I am inserting a select statement like:

Select CAST([省] as NVARCHAR(max)) as [Receive_Area(CN)], CAST([市] as DC_Area(CN) from TableName

But when I checked the [Data Set] table, I see that the IncrementalQuery column has a value like:

Select CAST([?] as NVARCHAR(max)) as [Receive_Area(CN)], CAST([?] as DC_Area(CN) from TableName

Could you please help me how to show this Chinese values instead of a question Mark '?'

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user15488631
  • 85
  • 2
  • 10
  • 1
    Does this answer your question? [SQL Server database field to handle korean and chinese characters](https://stackoverflow.com/questions/4736656/sql-server-database-field-to-handle-korean-and-chinese-characters) – Crispy Apr 14 '21 at 22:23
  • The incrementalquery column datatype is nvarchar(max) in my dataset table. While inserting i tried N''省'' and '省' but still they are showing as ? – user15488631 Apr 14 '21 at 22:29
  • The link didnt help me – user15488631 Apr 14 '21 at 22:31
  • @user15488631 - how are you "checking the [Data Set]"? Are you just running a SELECT statement in SQL Server Mgmt Studio? If so, it might be worth checking that your grid results are displaying in a unicode-capable font. (Tools -> Options -> Environment -> Fonts and Colours -> Show Settings for "Grid Results". Double-check that the font used there is one that can show the relevant characters ....... otherwise, the correct data might be stored in the database, but the font just shows "?" because it can't show the Chinese characters) – Craig Apr 14 '21 at 22:33
  • i tried changing to Gridresults and ran the insert statment but no luck. The gidresults doesnt show chinese chanracters over there. – user15488631 Apr 14 '21 at 22:44
  • @user15488631 - it may also come down to how you're actually doing the insert. For example, I tested a simple insert into a column - using INSERT INTO VALUES ('SELECT [省]'); and then INSERT INTO VALUES (N'SELECT [省]'); When I then select from the table, the first value I inserted has ?, the second shows the expected character. So, it may be that the string value you are insert into your [Data Set] table is not inherently unicode, so then SQL interprets it in its default fashion, which results in the Chinese character being droppped – Craig Apr 14 '21 at 22:45
  • @user15488631, there's a question here that has some good descriptions of SQL's behaviour regarding unicode / non-unicode strings https://softwareengineering.stackexchange.com/questions/155859/why-do-we-need-to-put-n-before-strings-in-microsoft-sql-server, but you'd likely be able to find plenty of different references about it with an online search, that would suit your particular situation (as I say, you don't specify how the actual insert is occurring) – Craig Apr 14 '21 at 22:45
  • Hi Craig thanks for the help i fallowed your instructions it worked INSERT INTO VALUES (N'SELECT [省]') Thank you for your help, – user15488631 Apr 14 '21 at 22:53
  • @user15488631 - Good news! – Craig Apr 14 '21 at 23:05

1 Answers1

1

If it's just a straight SQL INSERT that you're running, then I suspect the statement will need to be something like this:

INSERT INTO [Data Set] (Id,IncrementalQuery)
VALUES (1, N'Select CAST([省] as NVARCHAR(max)) as [Receive_Area(CN)] ,CAST([市] as DC_Area(CN) from TableName');

(Don't know whether your Id column is auto-incrementing or not - obviously, if it is, then it doesn't need to be included in the column list, or given a value)

The "N" prefix on the string explicitly tells SQL to treat the string as unicode

Craig
  • 1,123
  • 3
  • 13