Problem solve Get help with specific problems with your technologies, process and projects.

Finding ASCII representations of integers via SQL

Why and how to use the CHR() function to write single tics into SQL statements.

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close