UNISTR - Convert Unicode Codes to String - Oracle to SQL Server Migration

In Oracle, UNISTR function converts a string literal containing Unicode code points represented as '\hhhh' (hhhh is a hex value) as well as regular characters to Unicode string.

In SQL Server, you can use an expression using NCHAR function and N'string' literals.

Oracle:

  -- Convert Unicode code point 192 (hex value) to Unicode character (returns: ƒ)
  SELECT UNISTR('\0192') FROM dual;
 
  -- Convert a string containing regular char and Unicode code points (hex values) to Unicode string (returns: aƒΣ)
  SELECT UNISTR('a\0192\03A3') FROM dual;

SQL Server:

  -- Convert Unicode code point 192 (hex value) to Unicode character (returns: ƒ)
  SELECT NCHAR(0x192);    -- or NCHAR(402) with equivalent decimal value
 
  -- Build a string from regular char and Unicode code points (returns: aƒΣ)
  SELECT N'a' + NCHAR(0x192) + NCHAR(0x3A3);

UNISTR Conversion Overview

Oracle UNISTR to SQL Server conversion summary:

Oracle SQL Server
Syntax UNISTR('string') Expressions using NCHAR and N'string'
Unicode Code Representation '\hhhh' (hex value) 0xhhhh (hex value) or equivalent decimal code
Multiple Unicode Code Points NCHAR for each code point
Mixing Characters and Code Points Use NCHAR and N'string'
Alternative NCHR(code) for single code
Reverse - Get Code from Char ASCIISTR for string UNICODE for single character

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

Converting UNISTR Function from Oracle to SQL Server

In Oracle, a parameter of UNISTR function can contain Unicode code points encoded as \hhhh (hex value) as well as not encoded characters:

Oracle:

   -- Convert a string containing characters and Unicode code points (hex values) to Unicode string (returns: aƒΣ)
   SELECT UNISTR('a\0192\03A3') FROM dual;

In SQL Server, you have to use NCHAR function for each code point, and use concatenation operator + to build a string:

SQL Server:

  -- Build a string from regular char and Unicode code points (returns: aƒΣ)
  SELECT N'a' + NCHAR(0x192) + NCHAR(0x3A3);

Resources

SQLines Services

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.

You could leave a comment if you were logged in.