REPLACE function doesn't always work

[adi rusu]

Hi, I'm trying to replace the ' character inside a varchar field with another character (such as `). For example, in the database I have a record of the form "Hello, I'm happy to meet you". I apply the REPLACE function to this record: REPLACE(<table_name>.<field_name>, '''', '`'). Theoretically, the result should be "Hello, I`m happy to meet you". But it's not. The record is not affected by the REPLACE. Does anyone know a logical explanation? Or is this voodoo? :P Thanks

[PIEBALDconsult]

But you already have the correct character in the field, why replace it with an incorrect one? Are you setting the field to the new value? **<table_name>.<field_name> =** REPLACE(<table_name>.<field_name>, '''', '`')

[adi rusu]

Why is ` incorrect? I want to change the ' inside the varchar field because i want to generate an insert command, for example, inside a text file. If i don't change it, the command in the file would be something like this: INSERT INTO some_table VALUES (..., 'Hello, I'm happy to meet you') And my problem is that the string ends right after "I". I also tried doubling the ' character but it didn't work that way either.

[PIEBALDconsult]

th3_d3vil wrote:

doubling the ' character but it didn't work

It should. What is happening? Are you storing commands in a file? Why not store the data as CSV and have a parameterized insert command? What task are you trying to accomplish?

[adi rusu]

I found the problem. In the database I found ’ (ASCII code 22) instead of ' (ASCII code 39) and it seems that writing the results to the file using bcp changes the ’ (22) character with ' (39) :wtf: . It's funny that I don't seem to find ’ (22) on my keyboard ;P . Anyway thanks for your help :)