remove ' apostrophe from records SQL??

i'm trying to remove all the ' from a dataset

update A set room = replace (' ' ', '') where room like '% ' %'

but you can't because ' is reserved in oracle to delimit strings and characters..

how would i be able to do this???

Assuming that you are trying to update all the rows in a table which contains the " ' " you dont need to specify the where clause. only specify the where clause if you are limit the contents of a table based on a specific criteria. based on the above asumption here is the update statement you need to execute.

update A set room = replace(room, '''','');
(an update without the where clause goes thru the whole table, however you could still specify the where and do the same too.)

if you noticed in the replace function you did not specifiy the string to be replaced in the first place. also you specified 3 apostrophe, you need four . 1st one for opening the string, 2nd one to specify the escape character, 3rd one the apostrophe itself and 4th to close the string.

hth,

Jiggee