Escape characters in CLPPlus

You can use escape characters in CLPPlus commands and queries.

Description

In CLPPlus, you can use the ampersand (&) character to substitute variables in SQL statements. Escape characters can be used to escape the ampersand character in input values to avoid substitution, for example "AT&M".

Escape characters can also be used to escape the characters "$" and "%", which are used to reference shell and environment variables in CLPPlus.

You can define the escape character with the SET command. The default escape character is "\". For more information about the SET command, see the related reference.

Examples

  1. This example shows the use of the default escape character which avoids "&M" being treated as a substitution variable.
    SQL> set escape ON
SQL> insert into testtab values('AT\&M'); 
DB250000I: The command completed successfully.

SQL> select * from testtab;
TEXT
-------------------
AT&M
  2. This example shows the use of a user-defined escape character which avoids "&G" being treated as a substitution variable.
    SQL> set escape ^
SQL> insert into testtab values('L^&G');
DB250000I: The command completed successfully.

SQL> select * from testtab;
TEXT
-------------------
AT&M
L&G
  3. This example shows the behavior when no escape character is used. "&V" is treated as a substitution variable and requires the user to provide the input value of "Planet".
    SQL> set escape OFF
SQL> insert into testtab values('Smarter &V');
Enter a value for variable V: Planet

Original statement: insert into testtab values('Smarter &V')
New statement with substitutions: insert into testtab values('Smarter Planet')
DB250000I: The command completed successfully.

SQL> select * from testtab;
TEXT
-------------------
AT&M
L&G
Smarter Planet
  4. This example shows the behavior when no escape character is used. "&V" is treated as a substitution variable and requires the user to provide the input value of "Gene".
    SQL> set escape OFF
SQL> insert into testtab values('Blue \&V');
Enter a value for variable V: Gene

Original statement: insert into testtab values('Blue \&V')
New statement with substitutions: insert into testtab values('Blue \Gene')
DB250000I: The command completed successfully.

SQL> select * from testtab;
TEXT
-------------------
AT&M
L&G
Smarter Planet
Blue \Gene
  5. This example shows the behavior when an escape character is used. "$100" is treated as a value and not a shell or environment variable.
    SQL> set escape ON
SQL> insert into testsub values('\$100');
DB250000I: The command completed successfully.

SQL> select * from testsub;
TEXT
-------------------
$100
  6. This example shows the behavior when an escape character is used. "86%" is treated as a value and not a shell or environment variable.
    SQL> set escape ON
SQL> insert into testsub values('86\%');
DB250000I: The command completed successfully.

SQL> select * from testsub;
TEXT
-------------------
$100
86%