6

I have column with values that have wrong character ?. Now I want to change it to character b. For this I am using this statement:

SELECT REPLACE(name,'?','b') from contacts;

But when I do this nothing's happening, it return value with ?.

What I am doing wrong? How I can replace this?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
user238271
  • 643
  • 3
  • 11
  • 27
  • 5
    Let's be clear: did you want the resultset to have the modification, or did you want the table's data in the `name` column to be overwritten permanently with the `?` with a character `b`? – p.campbell Oct 19 '10 at 22:42
  • 3
    Are you sure that the character `?` actually exists in the data? In SSMS (default settings) "Results to Grid" mode I find that sometimes it displays `?` for unicode characters that it displays properly in "Results to Text" mode. – Martin Smith Oct 19 '10 at 22:49

4 Answers4

7

Are you actually trying to change the values in the table? In that case, you'll need to do an UPDATE:

UPDATE contacts
SET name = Replace(name,'?','b')

Otherwise, if you are simply trying to retrieve a modified value, your syntax should work just fine. (I tested it, ? doesn't have to be escaped or anything):

SELECT name, Replace(name,'?','b') as Fixed
FROM contacts
BradC
  • 39,306
  • 13
  • 73
  • 89
6

Another possibility that I've seen before is that the character looks like a regular old ASCII question mark but it's not really. It's actually a different character. I'd select the text and paste it into Notepad and then copy and paste it into my query.

Russell McClure
  • 4,821
  • 1
  • 22
  • 22
4

If your name column data type is NVARCHAR you should use N prefix. NVARCHAR and VARCHAR types have unicode differance. Look at this link for more information about differance between NVARCHAR and VARCHAR types.

SELECT REPLACE(name,N'?', N'b') from contacts;
Community
  • 1
  • 1
Mehmet Otkun
  • 1,374
  • 9
  • 22
0

Try this

update contacts set name=replace(name, '?', 'b')
shoebox639
  • 2,312
  • 15
  • 14