Character Count Problem

[UnknownProgrammer]

I am attempting to do a search & replace where every occurence of " (double quote) is replaced by ' (a single quote).

I am using the code ...

update Table set Column = replace(Column,'"','''')

I am running a query to get the length of "Column" in every record both before & after the replace happens in order to reconcile the before & after for the auditors.

They never match!

What am I doing wrong?
How can I do this?

Thanks for the help.

 

[SQLSister]

Testing that code, it appears to work, perhaps it is the code you wrote to get the lengths which is wrong.

"NOTHING is more important in a database than integrity." ESquared

 

[UnknownProgrammer]

Thank you for responding.

The query I used for the test is the following...

select sum(len(rtrim(Column))) as count from Table;

Is there any other way to do it?

Thanks for the help.

 

[gmmastros]

Is it possible that someone is modifying the data between the statements?

I'm guessing that you are getting the total character count, then updating the data, and finally getting the total character count again. Suppose someone were to insert/update/delete data after you get the first count but before you get the second count. In that case, you would expect the values to not match, right?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[UnknownProgrammer]

Sorry, should have included that the front end application is down.

I took a backup of the table & then performed the find & replace on the production table. When the numbers didn't match, I copied the backup into production & tried again. The results were the same.

 

[bborissov]

What this code gives to you:

SELECT Column
FROM YourTable
WHERE len(rtrim(Column)) <> len(rtrim(REPLACE(Column,'"','''')))

?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[UnknownProgrammer]

bborissov, The query you supplied returns no records.

 

[SQLSister]

You say the lengths never match between the backup table and the production table. Are you sure the two tables have the same datatype for that column or is one char and one varchar?

"NOTHING is more important in a database than integrity." ESquared

 

[UnknownProgrammer]

Tha backup table is was created by me today as a direct copy from the production table using the export data wizard in SQL Server 2000.