Replacing a single quote in SQL statement with two single quotes

[SJSFoxPro]

As part of a script that I run, I spool to a log file. The name of the log file includes a literal value entered
by the user. In some cases, this literal value must include a single quote (aka an apostrophe), as in the case
of a last name like O'DOWD. In these cases, I need to include an additional single quote.

Here's part of the script:
ACCEPT for_user PROMPT 'Enter user ID: '
COLUMN dummy NEW_VALUE v_spool_name
SELECT 'LOG_'||UPPER('&for_user')||'_'||
TO_CHAR(SYSDATE, 'dd-MON-yyyy')||'.sql' dummy
FROM dual;
SPOOL c:\logs\&&v_spool_name

The following error is raised when the value JOHN.O'DOWD is entered:
ORA-01756: quoted string not properly terminated

The easiest solution is to enter 2 single quote when prompted (i.e., JOHN.O''DOWD).

But, if possible, I would like to use some logic to check the entry for the inclusion of a single quote and when the entered value includes a single quote, replace it with two single quotes. Is this possible within a select statement such as this? (There are subsequent uses of the entered value in the script using the same select method.)

Thanks for any help!

 

[SantaMufasa]

SJS,

The probable reason that you have not received any responses yet is because this is a surprisingly tough issue (to programmatically replace an apostrophe from a SQL*Plus-prompted ACCEPT statement). In fact, I cannot think of a solution for you under these circumstances.

This is a problem because, unlike most other vendor environments that allow a matched set of either single quotes or double quotes to bound a string, Oracle (in this example of "infinite stupidity") did not program that accommodation.

Although we can certainly easily accommodate the handling of an apostrophe that already exists in your data, I cannot envision a way to deal with an apostrophe in this (screen-prompted) instance.

Therefore, my suggestion is to instruct your users that enter this string to either:

1) omit the apostrophe altogether, or
2) enter two single quotes when they want only one, or
3) use a "back-single quote" [i.e., " ` ", which is chr(96), usually appearing on the keyboard below the tilde ("~") character, to the left of your "1" key on the top tier of your keyboard.]

Once you have an alternate character entered, it becomes a "piece of cake" to translate/replace the alternate character with an apostrophe.

Let us know your thoughts.

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]