0

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
2
  • 3
    I am guessing what you really are trying to do is replace any cases of multiple quotes with a single one? You can easily do this using the techniques from Jeff Moden here. Just replace the space with a quote and the concept is identical.
    – Sean Lange
    Jul 18, 2019 at 16:50
  • @SeanLange worked for me nearly! I just keep getting a extra space, SELECT --REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( LTRIM(RTRIM(address1)) ,'''',' '+CHAR(50)) ,CHAR(50)+' ','') ,CHAR(50)+'','''') ,LTRIM(RTRIM(CHAR(50)))+' ''','') AS CleanString FROM contacts where address1 like '%''''%'
    – Rachel
    Jul 19, 2019 at 11:20

3 Answers 3

1

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.

0

What you are looking for could be accomplished with a CLR Procedure written in dotnet. You can then use a regular expression to match and replace on the column(s) you want to correct.

There are a couple of things to keep in mind with this approach.

  1. CLR procedures are a bit more complicated to create and update then just plain T-SQL.
  2. You will loose parallelism, but if this is only run occasionally then it probably won’t matter.
  3. They make debugging a bit more difficult.
0

Another non CLR option is to use a while loop. It won’t be very efficient as it will have to loop multiple times to remove any records that have more than one set of quotes to remove.

while (select count(*) from contacts where address1 like '%''''%') > 0
begin
BEGIN TRAN
       update contacts
       set address1 = REPLACE(address1,'''','')
       where address1 like '%''''%'

ROLLBACK
end
1
  • this nearly works except I need one quote to be left here, For example Unit Egan'''''''''s should be Egan's
    – Rachel
    Jul 19, 2019 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.