​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
The pesky single quote



You know when you're constructing strings in PL/SQL and you try and


concatenate say a varchar variable with a string, but the variable needs to be


enclosed in single quotes. You want something like,


     SELECT x FROM table1 WHERE z = '123'


where 123 is held as a variable, so you have to do something like this :-


     sql_select := 'SELECT x FROM table1 WHERE z = ' || '''' || :some_variable || '''';


How many single quotes do you need is it two, three - four?


Its messy so why not get the DECIMAL representation of a single quote and use


that with the CHR function instead. Get the DECIMAL value of the single quote

(or any other ASCII character) using:-

    SQL> SELECT ASCII('''') FROM DUAL; -> Answer 39

Yup, 4 quotes required - see what I mean !


Now all you need is something like.

DECLARE
qte varchar2(1) := CHR(39);
BEGIN
    sql_select := 'SELECT x FROM table1 WHERE z = ' || qte ||:some_variable || qte;
END;