How to find and replace multiple quote with single quote in table data

Question

By Mistake in my table multiple quote has been stored. suppose some time two double quote stored in item name like

Alzheimer''s Disease (AD)
OR
Alzheimer'''s Disease (AD)
OR
Alzheimer'''''''''s Disease (AD)

Now i want to execute a sql query which will find more than one single quote in itemname column and replace those multiple quote with single quote.

This way i can update double quote with single quote

UPDATE MyTable SET ItemName=REPLACE(ItemName,'''',''')

I assume the above statement will not do what i am trying to do.

i want to find and replace multiple quote with single quote.

if there are two quote or 8 quote or 5 quote means multiple quote. more than one quote will be replace with single quote in item name column. so please tell me what query does this job nicely.

i already mention though that no of quote is unknown in my case which is stored in item name column of table.

so how to detect which data has quote more than one and update accordingly ?

please suggest some code example. thanks

Answer 1

While waiting for a better tip, try this:

SET QUOTED_IDENTIFIER OFF
declare @t table
(
    Field varchar(50)
)
insert into @t (Field) values 
      ("Alzheimer''s Disease (AD)")
    , ("Alzheimer'''s Disease (AD)")
    , ("Alzheimer'''''''''s Disease (AD)")

-- before
select * from @t

WHILE (select max(charindex("''",Field)) from @t) > 0
    update @t set Field = replace(Field,"''","'")   

-- after
select * from @t

Answer 2

Using SET QUOTED_IDENTIFIER OFF is not a very good idea, although it is handy to add the test data in this case. But it should not used for the solution as such, as this is a legacy setting you should avoid.

Here is a different solution that makes use of the trim() function added in SQL 2017. It has a WHILE loop, but as long as there is only one sequence of multiple quotes, there will only be one iteration.

In the sample below, I have introduced a variable @sq to preserve our sanity.

DECLARE @t TABLE(Field varchar(50))

DECLARE @rowc int = 1,
        @sq  char(1) = ''''

INSERT @t (Field) VALUES 
      ('Alzheimer**s Disease (AD)')
    , ('Alzh''eimer***s Diseas*e (AD)')
    , ('Alzheimer*********s Disea****se (AD)')

UPDATE @t SET Field = replace(Field, '*', @sq)

-- Before
SELECT * FROM @t

WHILE EXISTS (SELECT * FROM @t WHERE Field LIKE '%' + @sq + @sq + '%')
BEGIN
   UPDATE @t
   SET    Field = substring(Field, 1, charindex(@sq + @sq, Field)) +
                  trim(@sq FROM substring(Field, charindex(@sq + @sq, Field) + 1, len(Field)))
   WHERE  Field LIKE '%' + @sq + @sq + '%'
END

SELECT * FROM @t

Answer 3

Hello SudipBhatt.
In the first solution I forgot the WHERE clause to limit processing to strictly necessary records.
I propose another solution that uses it.

declare @a char(1) = ''''
declare @t table
(
     Field varchar(50)
)
insert into @t (Field) values 
      ('Alzheimer''''s Disease (AD)')
    , ('Alzheimer''''''s Disease (AD)')
    , ('Alzheimer''''''''''''''''''s Disease (AD)')

-- before
select * from @t

WHILE (select count(*) from @t where Field like '%' + @a+@a + '%') > 0
    update @t set Field = replace(Field,@a+@a,@a) from @t where Field like '%' + @a+@a + '%'    

-- after
select * from @t

Answer 4

Hi SudipBhatt,
thanks for the question that allows you to explain.
I hope I can explain how this works with an example.
Consider this code:

declare @s varchar(max)
declare @s1 varchar(max)
declare @s2 varchar(max)
declare @s3 varchar(max)

set @s = 'Alz''''heimer''''s Dis''''eas''''e (AD)'

-- First step
select @s1 = replace(@s, '''', '+-');

-- Second step
select @s2 = replace(@s1, '-+' , '');

-- third step
select @s3 = replace(@s2, '+-', '''');

select 'Original string: ' + @s
union all
select 'First step: ' + @s1
union all
select 'Second step: ' + @s2
union all
select 'Third step: ' + @s3

In it this happens:

a) Original string
Alz''heimer''s Dis''eas''e (AD)

b) Replaces each ' with +-

Alz+-+-heimer+-+-s Dis+-+-eas+-+-e (AD)

c) Removes any -+
In this way there will remain as many +- as the single quotes are

Alz+-heimer+-s Dis+-eas+-e (AD)

-- Finally, therefore, just replace each sequence +- with '

Alz'heimer's Dis'eas'e (AD)

The + and - signs are not magic symbols, but only placeholders for the purpose of making the game.
You can choose any other pair of symbols; what matters is that these characters must not be in the string.
Although I am fascinating, I do not like this technique and I always prefer to solve problems in a clear and easy to read way. This is for the benefit of clarity, scalability and code sharing.
Sorry if I wasn't clear, but I use the google translator to be able to write in English!

Answer 5

IN SO another dev said to use this below approach to replace multiple quote with single quote but his '+-' & '-+' usage not clear. he said this is mirror replace. so when i search google with keyword mirror replace i found no relevant write up.

so anyone from this forum can tell me why the objective of using these characters '+-' & '-+' to replace multiple quote with single quote. thanks

select mycol, replace(replace(replace(mycol, '''', '+-'), '-+', ''), '+-', '''') mycol_new
 from (values 
     ('Alzheimer''s Disease (AD)'), 
     ('Alzheimer''''s Disease (AD)'), 
     ('Alzheimer''''''''''s Disease (AD)')
 ) t(mycol)