John Mercier

Hello everyone, I just wanted to post something cool I figured out at work.

Problem: You need to copy one field in a table to another field in the same table for all rows but that field has single quotes.

Solution: Well it looks like you will need to select everything in field one and create an update command to copy it to field two. To select everything in field one you can use

select field1 from tablename;

but this does not create the update statements needed. Try adding a string

select 'update tablename set field2=''' + field1 + '''' from tablename;

Now you will have to copy the output of this query into a new query and run it.

update tablename set field2='John's' from tablename;

You should get an error because of the single quote. It will tell the server that the string has ended and the rest is part of the query command. So, 'John' is the string and s' is just a syntax error. Single quotes can be "escaped" by using two single quotes instead of one. So, John's will become John''s. To do this within a query you can use the replace function in the first query.

select 'update tablename set field2=''' + replace(field1, '''', '''''') from tablename;

The result will be

update tablename set field2='John''s' from tablename;

The real benifit of using this method is when there are large text fields that need to be copied. It can also be useful when there are hundreds of records to be moved. The only problem I see with this method is the large amount of text it can generate for each update command. It may be possible to use the in statement to select the text from the database rather than putting it all into the query.