0

On my database I have had an issue with how I have escaped single quotes, resulting in them being doubled up every time a record is saved. I have fixed this now, but I need to clean up the mess it has created.

So I was wondering what's the most straight forward to replace all instances of more than on apostrophe with a single one. So for example replace ''''''' with '

There are many instances of varying numbers of them in the table.

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
Brian
  • 101
  • 9

2 Answers2

4

Try this trick below

UPDATE yourTable
SET columnName = REPLACE(REPLACE(REPLACE(columnName,'''','<>'),'><',''),'<>','''')

This trick comes in handy for replacing any character's multiple consecutive instances with corresponding single instance. I found this on SO for replacing multiple instance of space characters with single space .

Community
  • 1
  • 1
Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
2

This involves lots of single quotes:

update t
     set col = replace(col, '''''', '''');
-----------------------------^^**    ^^

The first string of single quotes has six single quotes, the second has four.

The ^^ and ** show the doubled single quotes that represent a single quote.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks but this would only work for that specific number of quotes ? I want a solution that will replace all instances. So for example both ''' and '''''''' would be replaced with a single quote. – Brian Dec 06 '16 at 12:15
  • I think this will replace any number of occurences that have 2 singel quotes. The replace function finds the first 2, replaces it with one, than finds the next 2, and so on... – GuidoG Dec 06 '16 at 12:23
  • @Brian . . . I guess when I've faced this problem with single quotes, it has only been two in a row. mhasan's solution works for any number. – Gordon Linoff Dec 06 '16 at 12:57
  • Thanks for your suggestion anyway Gordon. – Brian Dec 07 '16 at 12:15