Finding ASCII representations of integers via SQL

• Karen Porter
• Published: 26 Feb 2002

Often I want to include a carriage return or other character into my SQL statement. Single tics are especially confusing due to breaking them on and off to be effective. I now use the CHR() function to write single tics into my SQL. If I forget what integer is required in the CHR() function, I can use the script below to determine what integer I should use.

--------------------------------------------------------------------- 
-- Script Name: find_chr_val.sql 
-- Author: Karen Porter 
-- Date: February 20, 2002 
-- Purpose: Determine integer required to display specific characters 
-- Explanation: CHR(34) returns the ASCII representation of that integer. 
-- IE: chr(34) displays " on my system. 
-- Limitations: 
-- 1) Run as system or user with select privilege on dba_objects and dba_source 
-- 2) Created on v8.1.7.2 of Oracle RDBMS 
-- 3) If searching for ', you must type twice. IE: '' 
-- Flow: 
-- 1) Find the specific integer to display the character you choose 
-- 2) Display all chr(integers) and their corresponding ASCII values 
--------------------------------------------------------------------- 

-- Adjust SQL Plus Settings 
set echo off 
clear screen 
set linesize 80 
set feedback off 
set verify off 

-- Accept user input 
accept inpt char prompt 'Enter Character You Wish to Find CHR() Value For: ' 

-- Format Column Headings 
column valu format A12 heading 'Value Found' 
column chrctr format A13 heading 'For Character' 

-- Determine correct integer for character given 
-- LINE field from DBA_SOURCE where NAME=DBMS_UTILITY is queried 
-- but any table can be used as long as the field has > 256 values 
-- and those values are consecutive 
select lpad(line,12) as valu, lpad(chr(line),13) as chrctr 
from dba_source 
where name = 'DBMS_UTILITY' 
and line < 257 
and chr(line) = '&inpt'; 

-- Accept User Input 
accept full char prompt 'Full Listing? (y/n):  ' 

-- Adjust SQL Plus Settings 
clear screen 
set head off 

-- Prompt user to press enter because pause is being turned on 
select 'Press Enter to Continue or End...' 
from dual 
where upper('&full')='Y'; 

-- Adjust SQL Plus Settings 
set head on 
set pagesize 20 
set pause on 

-- Format Column Headings 
column fullvalu format A60 heading '
 
   Press Enter to Continue... ' 

-- Show a full listing of integers and corresponding chr() characters 
-- This time I used a rownum on dba_objects table 
-- Any table could be used as long as the table 
-- Has a minimum of 256 records 
-- The dashes will identify characters such as TAB or Carriage Return 
select rownum||'  -'||chr(rownum)||'-' as fullvalu 
from dba_objects 
where rownum < 256 
and upper('&full')='Y'; 

-- Adjust SQL Plus Settings 
set pause off 
set feedback on 
set verify on 
set echo on 

 

Reader Feedback

Stephan B. writes: This is an interesting approach, but here's an easier way that uses the following anonymous PL/SQL block:

set serverout on size 1000000
begin
 dbms_output.put_line('ASCII codings on this database');
 dbms_output.put_line('=============================='||CHR(10));
 dbms_output.put_line('Ascii   Character');
 dbms_output.put_line('------- ---------');
 for i in 1 .. 256 loop
  dbms_output.put_line(rpad(to_char(i, '099'), 7, '.')||': '||CHR(i));
 end loop;
end;
/

The disadvantages are 1) all ascii characters are shown in one go (... but I do not care, because I can easily scroll back on my windows bar, be it in a Unix or NT environment); and 2) Okay, single character inquiries are not implemented (yet ;-).

The advantages are 1) I can run this as any user -- I do not need any specific db privilege (other than CREATE SESSION); 2) runs under any Oracle version (7.*, 8.0.*, 8.1.*, 9.0.*); 3) Representation of digits is correct regarding Unix notation; and 4) it's a quick way to test the full set of the current national character setting on your DB instance.

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.