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"];

-- 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];

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

-- 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)];

-- 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; -- 👾