Re: SQL problem: How replace all ' with " from a column in a table?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 15 Dec 2006 18:41:07 -0800
Message-ID: <1166236867.109049.44730@f1g2000cwa.googlegroups.com>

John wrote:
> Hello,
> we have Oracle 9, and now I'm faced with this problem:
>
> I have to update an entire column from a table which contains the
> character
>
> ' (single quotes)
>
> and replace it with
>
> " (double quotes)
>
> The column is about "Descriptions", and now the customer wants to
> replace all occurences of the single quote with the double quote.
>
> If it can help you I give you the names:
>
> - table name: EVENT_LOGS
> - column name: DESCRIPTION
> - current character (eliminate): '
> - to replace character: "
>
> Can somebody please indicate me an SQL statement for updating the table
> a replace all single quotes with double quotes?
>
> Thank you very much for your help and have a good day
> John

Try executing the following:
SELECT

  CHR(39) SINGLE_QUOTE,
  CHR(34) DOUBLE_QUOTE,
  CHR(39) || 'TEST' || CHR(39) TEST,

  REPLACE(CHR(39) || 'TEST' || CHR(39), CHR(39), CHR(34)) REPLACEMENT FROM
  DUAL; If it works correctly, the first column should be a single quote, the second column should be a double quote, the third column should be the word TEST inside single quotes, and the forth column replaces the single quotes in the third column with double quotes.

If the above works, try this to make certain that the replacement is correctly taking place:
SELECT
  REPLACE(CHR(39) || DESCRIPTION || CHR(39), CHR(39), CHR(34)) REPLACEMENT
FROM
  EVENT_LOGS
WHERE
  DESCRIPTION LIKE '%' || CHR(39) || '%'; If the above works, try this:
UPDATE
  EVENT_LOGS
SET
  DESCRIPTION=REPLACE(CHR(39) || DESCRIPTION || CHR(39), CHR(39), CHR(34))
WHERE
  DESCRIPTION LIKE '%' || CHR(39) || '%'; Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Dec 15 2006 - 20:41:07 CST