CODEWITHSUNDEEP
sqlsql-server-2005t-sqlsql-server-2008
user238271
user238271

Reputation: 643

Replace NOT working!

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?

Upvotes: 6

Views: 17008

Answers (4)

Mehmet Otkun
Mehmet Otkun

Reputation: 1384

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;

Upvotes: 4

BradC
BradC

Reputation: 39946

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

Upvotes: 7

Russell McClure
Russell McClure

Reputation: 4851

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.

Upvotes: 7

shoebox639
shoebox639

Reputation: 2312

Try this

update contacts set name=replace(name, '?', 'b')

Upvotes: 0

Related Questions