Cannot figure out how to write this script?

Hi was wondering if somebody could help on this one.
I have a table called LocalisationText and a field called "ResourceText".After localization bad data went into the ResourceText field (double and triple quotes instead of singles).

EG
Entrada ""{0}"" en ubicación """"{1}"""""
在或大约 ""{0}"" 的命令出错
User Post Code Group of Type ""{0}"" is ""{1}""

Now I have been asked to write a script that
converts all the double/triple quotes preceding {number} with single quotes .

The above corrected should look like

Entrada "{0}" en ubicación "{1}"
在或大约 "{0}" 的命令出错
User Post Code Group of Type "{0}" is "{1}"

Can you help?

Is this a text/ntext column or varchar/nvarchar column? Please take a look at PATINDEX function. You can use a pattern like '%""{[0-9]}""%' to locate the double-quotes you want to replace and then use updatetext for text/ntext or stuff for varchar/nvarchar. And you will need to perform this iteratively until you don't find any matches for the pattern. This will provide a replace with search based on a pattern behavior.|||

Hi there

thanks for your reply.

The column in question is an nvarchar 512.

I will look into patindex function .

Do you have a quick example how I could do it?

thanks

|||

You can then use just replace like:

declare @.s nvarchar(512), @.p int
set @.s = N'Entrada ""{0}"" en ubicación """"{1}"""""
在或大约 ""{0}"" 的命令出错
User Post Code Group of Type ""{0}"" is ""{1}""
'
print replace(replace(replace(@.s, '""', '"'), '""', '"'), '""', '"')

|||

Or you can do your update in a loop and additionally include required criteria

SELECT top 1 * FROM LocalisationText
WHILE @.@.ROWCOUNT > 0
BEGIN

UPDATE LocalisationText_Test
SET ResourceText = REPLACE(ResourceText,'''''','''')
WHERE ResourceText LIKE '%''''{[0-9]%'
OR ResourceText LIKE '%[0-9]}''''%'

END

GO

|||

thanks a lot for your reply that worked!!!!!!!

thanks again

|||You don't need the loop since REPLACE will replace all occurrences of the specified string. And the WHERE clause of UPDATE gets the qualifying rows. Just a simple UPDATE will suffice.