How to use REPLACE in a dynamic way How can I make the REPLACE function dynamic, by dynamic I mean that if for example one entry has 3 quotes and an other on has 5, I want it to cater for all situations  

BEGIN TRAN
       update contacts
       set address1 = REPLACE(address1,'''','')
       where address1 like '%''''%'

ROLLBACK
If you are willing to just handle cases where the number of single quotes is 15 or less, you can use this pattern:

update contacts
    set address1 =    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(address1,'''''''''''''''''',''),  -- Replace any instance of eight quotes
                                '''''''''',''),                           -- Replace any instance of four quotes
                            '''''',''),                                   -- Replace any instance of two quotes
                        '''','')                                          -- Replace any single quote
where address1 like '%''''%'


If someone has entered more than 15 quotes into one field in sequence, you probably need to verify if they accidentally died while holding down the key.