Removing quotation marks... 2

[DWag]

I have a sql 2005 table containing a field for item description, when importing descriptions with quotation marks now have extra quotes. I have two questions:

1. how do I replace the quotes as in the following text:
"30"" Nova Keyboard Tray" should be
30" Nova Keyboard Tray

2. How do I prevent this from happening again when I import the next time?

Thanks
Dedra

 

[genomon]

Using " to represent inches as part of data is a pretty bad idea. Can you normalize the table into a size field and a description field? It gets even ickier if you try to use the data to print in Access or Crystal.

When everything is coming your way, you're in the wrong lane.

 

[DWag]

Unfortunately not, this is a software package that we are uploading data into. We are in the procoss of implementing the new software.

Thanks

 

[genomon]

All I can think of is to use the REPLACE function.
Maybe replace "" with *, then replace the other double quotes with an empty string, then replace the asterisk with a double quote? Seems like there oughta be a more elegant approach somewhere....

When everything is coming your way, you're in the wrong lane.

 

[ESquared]

UPDATE Table
SET Column = Replace(Substring(Column, 2, Len(Column) - 2), '""', '"'
WHERE Column LIKE '"%""%"'

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[chamilz]

genomon

your suggestion works. i tried with the given text.

select replace(replace(replace( '"30"" Nova Keyboard Tray"','""','*'),'"',''),'*','"')

 

[genomon]

Personally I like esquared's idea, UNLESS the string can be something different the data you decribed exactly above.
Either way, glad you have a workaround!

When everything is coming your way, you're in the wrong lane.

 

[ESquared]

Using the replace-with-an-asterisk method assumes no valid data will ever have an asterisk in it. Additionally, if some data somehow has a single double-quote mark in it, that method will remove it improperly (instead of removing just the opening and close quote marks).

 

[genomon]

Another reason to use esquare's solution!
Nice. And a star, doggone it!


When everything is coming your way, you're in the wrong lane.

 

[chamilz]

E2

i also beleive your solution is simple and complete.

have a star!