Repalce function not working

[mkal]

I am trying to remove "question marks" (?) from a string of data.

Example:
SELECT REPLACE(<column_name>, '?', '') FROM <table_name>

However it does not remove the question mark (?). I also tried using CHAR(63) in place of '?' and it still doesn't work. Is there something special about "?" that doesn't allow the REPLACE function to work with them?

There is some weirdness involved here. The string in question is:

SYNTHETIC?/ PETROLEUM 2-CYCLE OIL

right after the "C" in SYNTHETIC and before the / there appears to be a space, but if you copy this (hidden character) and then run

SELECT ASCI('?') it returns 63

Running the following

SELECT CHAR(63) returns a ?

When viewing this product on the website it shows up as SYNTHETIC?/ PETROLEUM 2-CYCLE OIL. Any ideas what's going on and how can I fix this (yes I can delete it but I'd like to be able to fix this during runtime in case it comes back).

I am running SQL 2005 SP3 Developer Ed. on Windows 2003 sp2

 

[RiverGuy]

I've tried the following, and it does seem to remove it for me:

SELECT 'SYNTHETIC?/ PETROLEUM 2-CYCLE OIL'
SELECT REPLACE('SYNTHETIC?/ PETROLEUM 2-CYCLE OIL', CHAR(63), '')

 

[mkal]

Ok, I'll grant you that, but when pulling the value from the table it does not remove it i.e.

SELECT REPLACE(<column_name>, '?', '') FROM <table_name>

That select statement fails to remove the "?

 

[PWise]

I dont that it is a hidden char 63
the reson the displays 63 is because it is unknowne

 

[mkal]

Guess I'll have to dig a little harder to find out what it is...

Thanks

 

[markros]

Are you sure you wanted to remove "?" ? I don't see it in the string above, I only see / character (forward slash).

 

[PWise]

Select unicode( SUBSTRING ( fieldname , 10 , 1 ) )
from tablename
Where fieldname ='SYNTHETIC?/ PETROLEUM 2-CYCLE OIL'

 

[PWise]

try this copy the text to a file and run this vb code

Sub Readchar()
Dim str As String
Open "C:\asc.txt" For Input As #1
Do While Not EOF(1)
str = Input(1, #1)
Debug.Print AscW(str), str
Loop
Close #1
End Sub