Replace single quote in some text occurences

[Eymard]

Hi,

I'm trying to clean up my table with some entered data that has single quotes. I have created the following function to strip the single quote but it strips also normal text that has valid single quote in it. Anybody can help me replace single quotes within the url text and not those text i.e. 'bargain’s' that has valid single quotes? Thank you.

<cfscript> function killSingleQuotes(dbVal){
var outval = dbVal;
outval ="#ReReplaceNoCase(outval, '’/', '"/',"all")#";
outval ="#ReReplaceNoCase(outval, '’_top’', '"_top"',"all")#";
outval ="#ReReplaceNoCase(outval, '’', '"', "all")#";
return outval; }
</cfscript>

<cfset temp = "Where to bargain’s shop:<a href=’/things-to-do/venues/beacons-closet/4585/9812’ target=’_top’>beacon’s closet</a> in Williamsburg's<a href=’/things-to-do/venues/pearl-river/6226/12498’ target=’_top’>Pearl River Mart</a><a href=’/things-to-do/venues/strand-bookstore/5413/5355’ target=’_top’>Strand Bookstore</a><a href=’/things-to-do/properties/west-25th-street-flea-market/6770/’ target=’_top’>West 25th Street Flea Market</a> between Fifth and Sixth Avenues">

<cfset _sURL = "#killSingleQuotes(temp)#"><cfdump var="#_sURL#">

[talofer99]

Did you try to use the functiuon #HTMLCodeFormat()#, it turns the qoutes into html "signs" ...

Tal.

[AbhiFusion]

Hi,

You may also would like to use URLEncodedFormat() function which would take care of what you need...

[Eymard · Accepted solution]

I used the following to clean it up.

BEGIN TRANSACTION

UPDATE dbo.tips SET description = REPLACE(CAST(description AS VARCHAR(4000)),'href=’/', 'href=''/')

UPDATE dbo.tips SET description = REPLACE(CAST(description AS VARCHAR(4000)),'’ target=’_top’', ''' target=''_top''')

COMMIT TRANSACTION;

ROLLBACK TRANSACTION;