In Oracle Database, the ASCII() function returns the decimal representation in the database character set of the first character of its argument.

Syntax

The syntax goes like this:

ASCII(char)

Where char is of data type CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

ASCII() returns the numeric ASCII value of only the first character of this string.

Example

Here’s a simple example to demonstrate:

SELECT ASCII('Oracle')
FROM DUAL;

Result:

79

This tells us that the uppercase letter O has the numeric ASCII value of 79.

As mentioned, ASCII() only returns the ASCII value of the first character. Therefore, the following produces the same result:

SELECT ASCII('O')
FROM DUAL;

Result:

79

To demonstrate this further, let’s get the numeric ASCII value from each letter in the above string:

SELECT 
    ASCII('O') AS "O",
    ASCII('r') AS "r",
    ASCII('a') AS "a",
    ASCII('c') AS "c",
    ASCII('l') AS "l",
    ASCII('e') AS "e"
FROM DUAL;

Result:

    O      r     a     c      l      e 
_____ ______ _____ _____ ______ ______ 
   79    114    97    99    108    101 

Case Sensitivity

Uppercase letters have a different ASCII value to their lowercase equivalents.

Example:

SELECT 
    ASCII('R') AS "R",
    ASCII('r') AS "r"
FROM DUAL;

Result:

    R      r 
_____ ______ 
   82    114

A Database Example

Here’s an example of using ASCII() in a database query:

SELECT 
  first_name, 
  ASCII(first_name) AS "ASCII value of leftmost character"
FROM employees
FETCH FIRST 10 ROWS ONLY;

Result:

   FIRST_NAME    ASCII value of leftmost character 
_____________ ____________________________________ 
Ellen                                           69 
Sundar                                          83 
Mozhe                                           77 
David                                           68 
Hermann                                         72 
Shelli                                          83 
Amit                                            65 
Elizabeth                                       69 
Sarah                                           83 
David                                           68 

Rightmost Character

Here, I use the SUBSTR() function to return the rightmost character from each pet name, and then use it again with the ASCII() function to return the ASCII value for that character.

SELECT 
  first_name, 
  SUBSTR(first_name, -1) AS "Rightmost character",
  ASCII(SUBSTR(first_name, -1)) AS "ASCII"
FROM employees
FETCH FIRST 10 ROWS ONLY;

Result:

   FIRST_NAME    Rightmost character    ASCII 
_____________ ______________________ ________ 
Ellen         n                           110 
Sundar        r                           114 
Mozhe         e                           101 
David         d                           100 
Hermann       n                           110 
Shelli        i                           105 
Amit          t                           116 
Elizabeth     h                           104 
Sarah         h                           104 
David         d                           100

Empty Strings

Providing an empty string results in null being returned.

SET NULL 'null';
SELECT ASCII('')
FROM DUAL;

Result:

   ASCII('') 
____________ 
        null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Null Values

Passing null results in null:

SELECT ASCII(null)
FROM DUAL;

Result:

   ASCII(NULL) 
______________ 
          null

Missing Argument

Calling the function with without passing an argument results in an error:

SELECT ASCII()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ASCII()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

It’s the same when too many arguments are passed:

SELECT ASCII('a', 'b')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ASCII('a', 'b')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: