PostgreSQL - ASCII Function

When working with PostgreSQL, you might need to derive the ASCII (American Standard Code for Information Interchange) code of a character. The PostgreSQL ASCII() function is a handy tool for this purpose. In the case of UTF-8 encoding, the ASCII() function returns the Unicode code point of the character.

What is the ASCII() Function in PostgreSQL?

The ASCII() function in PostgreSQL is used to obtain the ASCII code of a character. If the input character is a part of the UTF-8 character set, the function will return its Unicode code point instead. This can be particularly useful for various text-processing tasks where you must manipulate or analyze character data.

Why Use the ASCII() Function?

Using the ASCII() function can be beneficial in several scenarios:

1. Data Validation: Ensure that input characters fall within a certain range by comparing their ASCII values.
2. Text Analysis: Analyze text data by examining the ASCII values of characters.
3. Sorting and Ordering: Custom sort orders based on ASCII values of characters.
4. Encoding Detection: Identify and handle different text encodings.

Syntax

ASCII(char)

Parameter

Let's analyze the above syntax:

• char: The char argument is a character for which you want to derive the ASCII code. It is also important to note that if the user passes a string to the ASCII() function, it will return the ASCII code of the first character.

PostgreSQL ASCII Function Examples

Let's look into some examples to see how the ASCII() function works in PostgreSQL.

Example 1: Basic Usage

The following statement uses the ASCII() function to get the ASCII code values of character A and a:

SELECT
    ASCII( 'Geeks' ),
    ASCII( 'G' );

Output:

Explanation: Here, both functions return 71, which is the ASCII code for the character 'G'.

Example 2: Working with UTF-8 Characters

The below statement uses the ASCII() function to get the Unicode code point of a UTF-8 character.

SELECT
    ASCII( '?' );

Output:

Explanation: In this case, the function returns 63, which is the Unicode code point for the '?' character.

Conclusion

The PostgreSQL ASCII() function is a powerful tool for deriving the ASCII code or Unicode code point of a character. This function can help you analyze and manipulate your text data efficiently for simple ASCII characters or more complex UTF-8 encoded characters. By understanding and utilizing this function, you can enhance your PostgreSQL data processing capabilities.