PostgreSQL CHR Function

Summary: in this tutorial, you’ll learn how to use the CHR() function to return the character corresponding to a Unicode (or ASCII) code point.

PostgreSQL CHR() Function Overview #

The CHR() function lets you get the character corresponding to a given Unicode (or ASCII) code point.

Here’s the syntax of the CHR() function:

CHR(n int) -> textCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• n is a Unicode or ASCII code of the character you want to return.

The CHR() function returns a single-character string corresponding to the code point. The CHR() function throws an error if the codepoint is out of range.

Basic PostgreSQL function example #

The following query uses the CHR() function to get the ASCII of the number 65:

SELECT
  CHR(65);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 chr
-----
 ACode language: plaintext (plaintext)

Retrieving a Special Character #

The following query uses the CHR() function to return the character that corresponds to the ASCII value 36:

SELECT
  CHR(36);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 chr
-----
 $Code language: plaintext (plaintext)

Retrieving a Unicode Character #

The following query returns the Unicode character of the number 9731:

SELECT CHR(9731);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 chr
-----
 ☃Code language: plaintext (plaintext)

Unicode 9731 corresponds to the snowman character '☃'.

Notice that psql tool will issue an error. You should run the query in the PostgreSQL Client that supports Unicode.

Adding Line Breaks to output #

The following example uses the CHR() function to add a new line character to a string:

SELECT
  'pgtutorial.com' || CHR(10) || 'PostgreSQL Tutorial' result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       result
---------------------
 pgtutorial.com     +
 PostgreSQL TutorialCode language: plaintext (plaintext)

In this example, the return value of the CHR() function represents a newline character.

Out-of-Range Values #

The following example throws an error because the input value is out of range:

SELECT
  CHR(-1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Error:

ERROR: character number must be positiveCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Handling Null Input #

The following example uses the CHR() function with NULL:

SELECT CHR(NULL);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 chr
------
 NULLCode language: plaintext (plaintext)

Summary #

• Use the CHR() function to convert an integer into its corresponding ASCII or Unicode character.