SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT N'T' + NCHAR(9) + N'A' + NCHAR(0x9) + N'B' AS [Single Decimal
or Hex Digit],

       NCHAR(0xF02) AS [Code Point
(from hex)],
       NCHAR(3842) AS [Code Point
(from decimal)],

       -- We are passing in "values", _not_ "escape sequences"
       NCHAR(0x0000000000000000000000F02) AS [BINARY / hex
"value"],
       NCHAR(0003842.999999999) AS [INT / decimal
"value"];


Single Decimal
or Hex Digit
Code Point
(from hex)
Code Point
(from decimal)
BINARY / hex
"value"
INT / decimal
"value"


T A B
༂
༂
༂
༂


-- The following two syntaxes work regardless of the database's default collation:
SELECT NCHAR(0xD83D) + NCHAR(0xDC7E) AS [UTF-16 Surrogate Pair
(BINARY / hex)],
       NCHAR(55357) + NCHAR(56446) AS [UTF-16 Surrogate Pair
(INT / decimal)],
       CONVERT(NVARCHAR(10), 0x3DD87EDC) AS [UTF-16LE bytes];


UTF-16 Surrogate Pair
(BINARY / hex)
UTF-16 Surrogate Pair
(INT / decimal)
UTF-16LE bytes


👾
👾
👾


SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS [Database Default Collation];


Database Default Collation


SQL_Latin1_General_CP1_CI_AS


-- The following syntax only works if the database's default collation supports
--   Supplementary Characters (starting in SQL 2012), else the NCHAR() returns NULL:
SELECT NCHAR(0x1F47E) AS [UTF-32 (BINARY / hex)],
       NCHAR(128126) AS [UTF-32 (INT / decimal)];


UTF-32 (BINARY / hex)
UTF-32 (INT / decimal)


null
null


-- Works if current database has a "_UTF8" default collation (starting in SQL 2019):
SELECT CONVERT(VARCHAR(10), 0xF09F91BE); -- UTF-8 bytes

-- Works in SQL Server 2019 and newer, regardless of database's default collation:
DECLARE @Temp TABLE
(
  [TheValue] VARCHAR(10) COLLATE Latin1_General_100_CI_AS_SC_UTF8 NOT NULL
);

INSERT INTO @Temp ([TheValue]) VALUES (0xF09F91BE); -- UTF-8 bytes

SELECT * FROM @Temp;
-- 👾
Msg 448 Level 16 State 2 Line 7
Invalid collation 'Latin1_General_100_CI_AS_SC_UTF8'.