Oracle CHR Function

One of the presentations I enjoyed at Rocky Mountain Oracle Users Group (RMOUG) Training Days 2010 was Stephen Jackson's presentation "Using SQL to Generate SQL." I have used some of the techniques he discussed in his presentation to have SQL generate SQL, but one thing I had not thought of doing was to use the CHR function to make scripts more readable. Although this post focuses on using Oracle's CHR string function implementation, other databases support the CHR (or CHAR) function as well.

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:

-- displayCHR.sql
--
-- 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
BEGIN
   FOR i IN 32..255 loop
      Dbms_output.put_line(i || ' ' || chr(i));
   END loop;
END;
/

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.


Conclusion

As Stephen pointed out in his presentation, the CHR function can make SQL*Plus scripts more readable and maintainable.