One example of the usefulness of CHR is in the creating of results that include characters that are significant in the query itself. Using CHR appropriately allows the SQL developer to avoid the need to escape these characters with query syntax significance. For example, to print the last names of employees in Oracle's HR sample schema with single quotes surrounding the last names, one could write a query like this:
select '''' || last_name || '''' from employees;
The four single quotes successfully escape the quote mark both before and after the last name. Unfortunately, it can become easy to get lost in the quotes, especially for more complex queries. Because the ASCII decimal code of 39 produces a single quote when passed to CHR, the expression
CHR(39)can be used instead as shown next:
select CHR(39) || last_name || CHR(39) from employees;
For me, this is more readable. Similarly, even more difficult characters can be represented with the CHR function. For example, Stephen pointed out in his presentation that CHR(10) can be used to have a new line printed in the output.
One of the interesting ideas that Stephen discussed was use of a simple script to display the various character representations available via the CHR function. One can always reference a resource like Tech on the Net's ASCII Chart or asciitable.com, but it is interesting to simply display the representations via code:
-- Display the characters associated with basic and extended ASCII codes.
SET head off
SET pagesize 0
SET linesize 120
SET trimspool on
SET feedback off
SET verify off
SET serveroutput on size 5000
FOR i IN 32..255 loop
Dbms_output.put_line(i || ' ' || chr(i));
The above snippet of code, when executed in SQL*Plus, will display a large number of the characters available in the basic and extended ASCII character set. One can use the spool command to spool the output to a file. On my Windows-based machine, I was even able to view these generated symbols in the spooled output file using the basic Notepad application.
As Stephen pointed out in his presentation, the CHR function can make SQL*Plus scripts more readable and maintainable.