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.