In Oracle Database, the ASCIISTR() function returns an ASCII version of the given string in the database character set. 

Syntax

The syntax goes like this:

ASCIISTR(char)

Where char is a string or an expression that resolves to a string, in any character set.

Non-ASCII characters are converted to the form \xxxx, where xxxx represents a UTF-16 code unit.

Example

Here’s an example:

SELECT ASCIISTR('Fish')
FROM DUAL;

Result:

Fish

In this case, the result is the same as the input, because the input used ASCII characters.

Here’s another one that uses non-ASCII characters:

SELECT ASCIISTR('ปลา')
FROM DUAL;

Result:

\0E1B\0E25\0E32

Here, we get each of the three characters returned in their ASCII equivalents.

In this case, the input (ปลา) is the Thai word for “fish”. This can be pronounced as “Plā”.

Here’s what happens when we pass Plā to the ASCIISTR() function:

SELECT ASCIISTR('Plā')
FROM DUAL;

Result:

Pl\0101

In this case, the first two characters that we passed are ASCII characters, but the third character is non-ASCII. Therefore, the function returns the first two characters unchanged, and the third one converted to ASCII.

Here’s another one that passes น้ำ, which is the Thai word for “water”:

SELECT ASCIISTR('น้ำ')
FROM DUAL;

Result:

\0E19\0E49\0E33

Null Values

If the argument is null, the result is null:

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

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever a null value 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.

Invalid Argument Count

Calling ASCIISTR() without any arguments results in an error:

SELECT ASCIISTR()
FROM DUAL;

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"