Oracle ASCII Function: A Simple Guide

The ASCII function in Oracle SQL is the reverse of the CHR function. It takes a character string and returns the numeric code (like ASCII, EBCDIC, etc.) for its very first character, based on the database's character set.

It's a useful function when you need to know the underlying numeric value of a character for validation, sorting, or comparison.

What is the ASCII Function in Oracle?

The ASCII function's only job is to inspect a string and tell you the decimal number that represents its first character.

This is useful for:

• Validation: Checking if the first character of a string is in a specific range (e.g., is it an uppercase letter?).
• Sorting Logic: Applying custom sorting rules based on character codes.
• Finding Non-Printable Characters: Identifying if a string starts with a non-standard or non-printable character.

Note: If your database uses a 7-bit ASCII character set, it returns an ASCII value. If it uses another set like EBCDIC, it will return the EBCDIC value for that character.

ASCII Function Syntax

The syntax for ASCII is very simple:

ASCII(char)

Let's break that down:

• char: The string or column you want to examine. Even if the string has many characters (e.g., 'Hello'), ASCII will only look at the first one ('H').

Oracle ASCII Function Examples

Here are two practical examples of how to use ASCII.

Example 1: Finding the ASCII Value of a Character

This example shows the most basic use of ASCII to get the numeric code for the letter 'A' and the number '1'.

Query:

SELECT 
  ASCII('A') AS "Code for A",
  ASCII('1') AS "Code for 1"
FROM DUAL;

Result: (Assuming a standard ASCII character set)

Code for A   Code for 1
----------   ----------
        65           49

Example 2: Using ASCII in a WHERE Clause

You can use ASCII to find data based on the numeric code of its first character. For example, the ASCII code for the capital letter 'A' is 65.

This query will find all employees whose last name starts with 'A'.

Query:

-- This finds all last names where the first character's
-- ASCII value is 65 (which is 'A').
SELECT 
  last_name
FROM employees
WHERE ASCII(last_name) = 65
ORDER BY last_name;

Result: (This would return a list of last names like 'Abel', 'Austin', etc.)