I have to do some work on a database that from a programming issue
several years ago, there are names in a table (business names) that have
a problem with apostrophes.

What happened is that in some instances, there are 2,3 4 and 5 quotes in
the names.

Instead of it being:

Bob's Hardware

it is:

Bob''''s Hardware

I need to use SQL to clean up these multiple instances of quotes in
certain fields of this table.

I use a function called OCCURS that I can identify which records have
this, but don't know how to change the table.

Any advice or ideas would be appreciated.

BC

I am assuming that this is the single quote character, ', rather than
an apostrophe.

The first thing to understand is that since the single quote character
is the string delimiter, to embed one in a string you have to use two
for each one.

CREATE TABLE Names(Name varchar(30))
INSERT Names VALUES('Bob''''''''s Hardware')

SELECT * FROM Names

UPDATE Names
   SET Name = REPLACE(Name, '''''', '''')
WHERE Name LIKE '%''''%'


SELECT * FROM Names

Name                          
------------------------------
Bob''''s Hardware

Name                          
------------------------------
Bob''s Hardware

This will only reduce the number of sequential occurances of the
character by half, so if there are more than two it will require
repeated execution of the UPDATE.

Roy Harvey
Beacon Falls, CT

On Fri, 04 Aug 2006 13:38:54 -0400, Blasting Cap
<goo***@christian.net> wrote:

Show quoteHide quote

Blasting Cap wrote:
Show quoteHide quoteIs it always four ' characters, or are there variable numbers?  If
always four, do this:

UPDATE table
SET name = REPLACE(name, CHAR(39) + CHAR(39) + CHAR(39) + CHAR(39),
CHAR(39))

Otherwise, do this, repeat until nothing new is updated:

UPDATE table
SET name = REPLACE(name, CHAR(39) + CHAR(39), CHAR(39))

Create view of winning lottery numbers
Performance issues using Parameters
Dynamic SQL Problem..
Timeout - max pool size reached
CLR and Insert