Oracle UNISTR Function: A Simple Guide

The UNISTR function in Oracle SQL is a powerful tool for creating text strings in the national character set. Its most important feature is its ability to create Unicode characters by specifying their hexadecimal "escape" code.

"UNISTR" stands for "Unicode String." You use it any time you need to create a string that contains special characters (like å, ñ, ö, or €) that might be difficult or impossible to type directly into your SQL editor.

What is the UNISTR Function in Oracle?

The UNISTR(char) function takes a standard text string and returns it as an NVARCHAR2 (the national character set data type).

During this conversion, it translates any 4-digit Unicode escape codes (in the format \xxxx) into their actual characters.

This is useful for:

• Ensuring your string is stored in a Unicode-compatible national character set.
• Inserting special symbols or international characters into an NCHAR or NCLOB column.

UNISTR Function Syntax

The syntax for UNISTR is:

UNISTR(char_string)

Let's break that down:

• char_string: The string you want to create. Any \xxxx codes (where xxxx is a 4-digit hex number) will be converted into their corresponding Unicode character.
• Escaping a Backslash: If you need to include a literal backslash in your string, you must "escape" it with another backslash. For example, to get the string C:\Temp, you would write UNISTR('C:\\Temp').

Oracle UNISTR Function Examples

Here are two practical examples of how to use UNISTR.

Example 1: Creating International Characters with UNISTR

This example shows how to build a string that contains both standard ASCII characters (abc) and special Unicode characters by providing their hex codes.

• \00e5 is the code for å
• \00f1 is the code for ñ
• \00f6 is the code for ö

Query:

SELECT
  UNISTR('abc\00e5\00f1\00f6') AS "Unicode_String"
FROM DUAL;

Result:

Unicode_String
--------------
abcåñö

Example 2: Creating a String with a Literal Backslash using UNISTR

This example shows how to correctly create a Windows file path by using a double backslash (\\) to get a single literal backslash (\) in the final string.

Query:

-- We must double the backslash to 'C:\\Temp'
-- to prevent UNISTR from thinking '\T' is a Unicode code.
SELECT
  UNISTR('C:\\Temp\\File.log') AS "File_Path"
FROM DUAL;

Result:

File_Path
-------------
C:\Temp\File.log