Cannot replace Char (63) with SQL query
I have several rows in a table with some unusual character. When I use ascii () or unicode () on this character, it returns 63. But when I try to do this -
update MyTable
set MyColumn = replace(MyColumn,char(63),'')
it does not replace. The fancy character still exists after the replace function. Char (63) accidentally looks like a question mark.
For example, my string is 'ddd # dd ddd' where # is my fancy character and
select unicode('#')
give me 63 back. but this code
declare @str nvarchar(10) = 'ddd#dd ddd'
set @char = char(unicode('#'))
set @str = replace(@str,@char,'')
works!
Any ideas how to fix this problem?
Additional Information:
select ascii(' ')
returns 63, like select ascii('?')
. Finally select char(63)
returns ?
and not a diamond question mark.
When this character is inserted in Excel or a text editor, it looks like a space, but in the SQL Server query window (and apparently also here on StackOverflow) it looks like a diamond containing a question mark.
source to share
Not only does char (63) look like '?', It is actually '?'.
(As a simple test, make sure you have numlock on your keyboard, press and hold the alt key and type "63" on your numeric keypad - you can all sorts of fun this way, try alt-205 then alt-206 and alt-205 again: ═╬═)
Is it possible that '?' you see, however, not char (63) and a more revealing character that SQL Server doesn't know how to display.
What do you get at startup:
select ascii(substring('[yourstring]',[pos],1));
--or
select unicode(substring('[yourstring]',[pos],1));
Where [yourstring]
is your string and [pos]
is the position of your char in the string
EDIT
From your comment it seems like it is a question mark. You tried:
replace(MyColumn,'?','')
EDIT2
Out of interest, what does the following do for you:
replace(replace(MyColumn,char(146),''),char(63),'')
source to share