Unable to replace Char(63) by SQL query

Question

I am having some rows in table with some unusual character. When I use ascii() or unicode() for that character, it returns 63. But when I try this -

update MyTable
set MyColumn = replace(MyColumn,char(63),'') 

it does not replace. The unusual character still exists after the replace function. Char(63) incidentally looks like a question mark.

For example my string is 'ddd#dd ddd' where # it's my unusual character and

select unicode('#')

return me 63.But this code

declare @str nvarchar(10) = 'ddd#dd ddd'
set @char = char(unicode('#'))
set @str = replace(@str,@char,'')

is working!

Any ideas how to resolve this?

Additional information:

select ascii('�') returns 63, and so does select ascii('?'). Finally select char(63) returns ? and not the diamond-question-mark.

When this character is pasted into Excel or a text editor, it looks like a space, but in an SQL Server Query window (and, apparently, here on StackOverflow as well), it looks like a diamond containing a question mark.

Answer 1

Not only does char(63) look like a '?', it is actually a '?'.

(As a simple test ensure you have numlock on your keyboard on, hold down the alt key andtype '63' into the number pad - you can all sorts of fun this way, try alt-205, then alt-206 and alt-205 again: ═╬═)

Its possible that the '?' you are seeing isn't a char(63) however, and more indicitive of a character that SQL Server doesn't know how to display.

What do you get when you run:

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. Have you tried:

replace(MyColumn,'?','') 

EDIT2

Out of interest, what does the following do for you:

replace(replace(MyColumn,char(146),''),char(63),'')

Answer 2

char(63) is a question mark. It sounds like these "unusual" characters are displayed as a question mark, but are not actually characters with char code 63.

If this is the case, then removing occurrences of char(63) (aka '?') will of course have no effect on these "unusual" characters.