Exclamation mark in sql string

Question

I got string in a table like this 'ABC !! DEF' When I do a Select in SSMS I see them. When I try to add them in Powerpivot, they disapear. when I try in SQL to modify my view with a simple

Replace(MyString, '!!',' ') the !! still there. I tried also with the char(33) and have the same issue.

I'm using SSMS on a SQL Server 2012

It's been a while that I search all around, so I need your help!!!!

Thanks!

Try to look at the bytes and see what is actually there. Just a shot in the dark but it may not really be ! characters — Alex von Brandenfels, Nov 20 '17 at 19:49
Are you sure it's `'!!'` (`CHAR(33) + CHAR(33)`) and not `N'‼'` (`NCHAR(8252)`)? — Bacon Bits, Nov 20 '17 at 20:22
It shows as !! and no ! !... but it's not NCHAR(8252). I just figured the thing out... I'll explain in this post as answer... it can help people in the future! Thanks for your help! — Marc Proulx, Nov 21 '17 at 19:26

S3S · Answer 1 · 2017-11-20T20:32:29.630

0

If that is a real string, test what the actual value is. Obviously it isn't an exclamation point. You can find out like this:

declare @str table (string nvarchar(64))
insert into @str
values
(N'ABC !! DEF'),
(N'ABC ‼ DEF')

select 
    ASCII(SUBSTRING(string,5,1))
   ,UNICODE(SUBSTRING(string,5,1))
from @str

Then, you will know what you need to replace. You may have to edit the substring to extract the value from your actual column. Without real sample data, this is the best I could assume.

edited Nov 20 '17 at 20:32

answered Nov 20 '17 at 20:24

S3S

24,809
5
26
45

1

[You're going to want to treat everything as an nvarchar here.](http://sqlfiddle.com/#!6/5a349/2) The table column should be an nvarchar, and you should be using the UNICODE() function, not ASCII(). – Bacon Bits Nov 20 '17 at 20:30

score 0 · Answer 2 · answered Nov 21 '17 at 19:36

Thanks for your help in this issue that I have. With all the try and proposition you made, that guide me to a solution. This is the explaination...

The problem is due to an exportation of data from a tool called Evolve (a apps library/product Catalog). It generate an Excel Extract of the data. I took for granted that the extract was good but I saw a weird behaviour. Some - was exported properly. But in same case the - was a copy paste from word that gives a longer -. In the Exportation, this long - gaves me the !!, which is a error in converting the character. I figured that out by copying directly the !! symbol in SQL (gives a blank) or Outlook (gives a square with a interrogation marks in it).

This Characters is CHAR(19). I just split the original string char by char to see the value.

I fix my problem by replacing the CHAR(19) by a -. I ask the product owner of Evolve to correct this behaviour.

Thanks again for your comments en suggestion!

Exclamation mark in sql string

2 Answers2