×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle: All versions FAQ

Tips and Tricks

PL/SQL and the pesky single quote by taupirho
Posted: 22 Nov 05

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;

Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close