Database Changes to remove [ square brackets ]

[DavidAM]

Hi,

I have a number of pages which have Full Descriptions containing [square brackets]. Since this causes problems with sellerdeck variables on some of these pages I'd like to find some way to run a database change in access to replace all [ with ( and ] with ) in the Full Description field only. I'm not very familiar with these types of queries and wonder if anyone can point me in the right direction?

I think I currently only use [LINK] in conjunction with discounts and not directly in the full description field of the actual products, so it shouldn't upset anything from that side.

I'm mainly getting the problem on contents lists for sheet music books we sell, although not always...

After Midnight [Clapton, Eric]
Bad Moon Rising [Fogerty, John]
Black Water [Doobie Brothers, The]
Doctor My Eyes [Browne, Jackson]
Don't Let The Sun Go Down On Me [John, Elton]
Down On The Corner [Creedence Clearwater Revival]
Europa (Earth's Cry, Heaven's Smile) [Santana, Carlos]
Fly Away [Kravitz, Lenny]
Gimme Some Lovin' [Spencer Davis Group, The]
Go Your Own Way [Fleetwood Mac]
Higher Love [Winwood, Steve]
Hotel California [Eagles, The]
I Want To Know What Love Is <Actinic:Variable Name = 'Foreigner'/>
I'm Still Standing [John, Elton]
Jump [Van Halen]
Layla [Clapton, Eric]
Long Train Runnin' [Doobie Brothers, The]
Lyin' Eyes [Eagles, The]
Maggie May [Stewart, Rod]
Money [Pink Floyd]
Moondance [Morrison, Van]
More Than A Feeling <Actinic:Variable Name = 'Boston'/>
Open Arms [Countdown Singers, The]
Proud Mary [Creedence Clearwater Revival]
Sister Golden Hair <Actinic:Variable Name = 'America'/>
Truckin' [Grateful Dead]
What A Fool Believes [Doobie Brothers, The]
You're My Best Friend <Actinic:Variable Name = 'Queen'/>

Thanks,


Dave

[NormanRouxel]

Not a solution but some additional info: SellerDeck won't expand [ something ] if you put spaces between the brackets and the inner-content (like I've done).

[Andy@codepath]

Execute these two queries one after the other. I'm sure you could combine it into one but simpler this way. Please note that this will replace all instances of [ with ( and ] with ), regardless of how many occurrences there are in the description or whether or not they are in pairs.

BACKUP FIRST

update product 
set [full description] = replace([full description], "[", "(")
where [full description] like "*[[]*"

update product 
set [full description] = replace([full description], "]", ")")
where [full description] like "*]*"

(P.S. Happy to help tidy up such a great list of songs :-)