SQL Server 2012 not showing unicode character in results

Question

All I want to do is update a field with the DIRECT CURRENT SYMBOL FORM TWO ⎓ character into my SQL Server 2012 database. Is that too much to ask? Apparently it is.

The answer to this question and this question is the same and did not work for me.

My update script

UPDATE Table 
SET Value = N'SUPPLY 9-30Vdc 0.2W ⎓' 
WHERE id = '1234'

Aaaaand the relevant table schema:

CREATE TABLE [dbo].[Table]
(
    ...
    ... 
    [Value] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    ...
    ...
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Want more? Here's the results of sp_columns:

Yes, I prefixed the string with N''. That should work, right? Nope.

Screenshot:

Notice the little box where the unicode character should be. Why, though?

@Aron Ah, I forgot to mention that. Yes, it is. I've updated my question. — LCIII, Oct 05 '16 at 19:15
I don't have an answer for you, but if I run a "select convert(nvarchar(1),'⎓') as test" I get a questionmark back instead of the unicode character so I think this is an issue with the SSMS not being able to interpret this data, beyond that I don't know. SQL 2016 DataCenter over here. Also, apparently this is ASCII CHAR(63) — Aron, Oct 05 '16 at 19:29
@Aron - wouldn't that be because you are passing in an ASCII value? Shouldn't it be `select convert(nvarchar(4),N'⎓')` ? — Igor, Oct 05 '16 at 19:32
derp; you are correct! Adding the N, now I see the same box in LCIII's post. — Aron, Oct 05 '16 at 19:33
@LCIII Works for me [here](http://rextester.com/BQPZOD42457). But I can't even use your code in my local SSMS, because it doesn't recognize the `⎓` even when I copy-paste it — Lamak, Oct 05 '16 at 19:34
Last post... according to this article, you must change your results to display as TEXT and not as grid. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b8b52b86-7dc9-4e59-9a54-9b826ca8e710/sql-server-2008-not-showing-and-inserting-unicode-characters?forum=sqldatabaseengine After doing that, I see the characters. — Aron, Oct 05 '16 at 19:34
@LCIII - Yes, its definitly persisted correctly. As to why your grid is not displaying it I am not sure. I am using the grid output as well in the same version of SQL Server and see the character just fine. — Igor, Oct 05 '16 at 19:37
@Igor When I update using `convert(nvarchar(4),N'⎓')` I still see a little box — LCIII, Oct 05 '16 at 19:38
@Aron Ah hah! When I output as TEXT instead of Grid I finally see the correct engineering symbol as well. So would that mean that my database truly is getting updated but the issue is with SQL Server's grid output itself? — LCIII, Oct 05 '16 at 19:40
@Aron You should post this information as the answer so I can upvote and accept. — LCIII, Oct 05 '16 at 19:42

score 7 · Accepted Answer · edited Feb 10 '19 at 15:10

7

Per the comment discussion on your question, your SQL code is correct and the unicode character is being updated, but SQL Server Management Studio cannot (for some reason) display this unicode character in the GRID resultset. If you change your result view to TEXT (control+T), you should see the unicode character.

"If you use SSMS for your queries, change to output type from "Grid" to "Text", because depending on the font the grid can't show unicode."

sql server 2008 not showing and inserting unicode characters

edited Feb 10 '19 at 15:10

Hadi

36,233
13
65
124

answered Oct 05 '16 at 19:45

Aron

765
6
14

2

Nice find, the selected font would make sense. – Igor Oct 05 '16 at 19:48
In Visual Studio, I tried changing the font for the results grid to Consolas (Tools → Options → Fonts and Colours → "Show settings for: SQL Results - Grid" → Font). However, it still didn't support Unicode characters in spite of the code window with the same font being able to display them. If anyone knows a working font then speak-up - but based on this, it doesn't look like a font issue. – Matt Arnold Sep 01 '20 at 13:51

score 2 · Answer 2 · answered Mar 06 '19 at 08:46

Thanks to @Aron, I was having next problem:

In SSMS, when I copy a text like Sighișoara from a grid result (table A from database A) and paste in an insert query (table B from database B), the result of this insert was Sighi?oara, with an interrogation character.

Steps that helped to me in SSMS:

Tools --> Options --> Environement --> Fonts and Colors. Select in Show settings for the option Grid Results and for this option, use an unicode font.
Be sure that your table has the column with nvarchar(X), not varchar(X).
Be sure that, when you insert data, you have N character before string, like this:

insert into table(a,b,c,d,e,f) values ('Rumania',N'Sighișoara',GETDATE(),0,0,'Test')

(For SSMS 17.9.1)

score 0 · Answer 3 · answered Oct 05 '16 at 19:43

You can try:

UPDATE Table SET Value = 'N' + char(39) + 'SUPPLY 9-30Vdc 0.2W ⎓' WHERE id = '1234'

Maybe:

SET QUOTED_IDENTIFIER OFF;

UPDATE Table SET Value = REPLACE(Value, "N'SUPPLY ", "SUPPLY") WHERE id = '1234'

SET QUOTED_IDENTIFIER ON;

I hope this helps...

Good Luck!

score -1 · Answer 4 · answered Oct 05 '16 at 19:40

-1

There is no column called "value". Does this work?

UPDATE Table SET COLUMN_NAME = N'SUPPLY 9-30Vdc 0.2W ⎓' WHERE id = '1234'

answered Oct 05 '16 at 19:40

Uwe E.

39
3

It works per Aron's comment in the original question – LCIII Oct 05 '16 at 19:41

SQL Server 2012 not showing unicode character in results

4 Answers4

Linked