escape characters - '

[DBrack]

Hello,

I am using a pl/sql procedure block to insert records into a table. Some of them have special characters like ' and - How can I escape them? Thanks.

Ex: pkgname.update('12345', 'aaaa-bbbb');

 

[Beilstwh]

The hyphens will not be a problem, but if you have an embedded quote, then you have to double. For example.

pkgname.update('12345', 'O''SMITH');

When pl/sql sees two single quotes next to each other within a quoted string, it rewrites it internally to be only one quote when the insert is done.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

DBrack,

As an alternative to Oracle's silly method of using two single quotes to result in one single quote, I would choose from either of the following "workarounds" to fool Oracle:

1) If your business/application has no problem representing an apostrophe/single quote as a "back tick" (`), meaning if [O`Reilly] is just as good as [O'Reilly], then just use the "back tick" (an ASCII 96) instead of a single quote (an ASCII 39).

2) If you business/application must use the single quotes when representing an apostrophe, then you can still "trick" Oracle with this simple invocation of a nearly-as-simple user-defined function:

Section 1 -- Invocation of Santa's "q" (meaning "quote")function:

select q('I`m hopin` that Bill O`Reilly won`t say nuthin` embarssin`.') from dual;

I'm hopin' that Bill O'Reilly won't say nuthin' embarssin'.

Notice that there are no annoying double single quotes to produce the desired results, which ultimately have the standard apostrophe/single quotes.

Section 2 -- Code that defines the "q" function:

create function q (str_in varchar2) return varchar2 is
begin
    return translate(str_in,chr(96),chr(39));
end;
/

Function created.

Let us know if this looks appealing.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]