I have been posting a lot of tech tips lately. As I've mentioned before, this is often for my own benefit so I can go back and review what I did when I have the same issue a year down the road.
Today's tip is for handling single quotes in PL/SQL. I'm working with Postgres 8, this may also apply to Oracle. I have a function that is taking a string value which was failing if that value had a single quote in it. I tried using the quote_ident, and quote_literal functions provided by PL/SQL but they didn't work as I would have expected, adding additional quotes to the ends of the value so I had to fall back to a string replace function:
REPLACE(column, '''', '''''')
This replaces all singe quotes within the string with two single quotes as needed.
Steven Reimer
