Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Convert unicode escape sequence to nvarchar RE: Convert unicode escape sequence to nvarchar

  • June 20, 2017 at 3:52 am

    #1947353

    Luis Cazares - Monday, June 19, 2017 1:22 PM

    I don't like the use of a scalar function, but this might help.

    DECLARE @m NVARCHAR(max)= N'\u0633\u0644\u0627\u0645';

    WITH
    E(n) AS(
      SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
    ),
    E2(n) AS(
      SELECT a.n FROM E a, E b
    ),
    E4(n) AS(
      SELECT a.n FROM E2 a, E2 b
    ),
    cteTally(n) AS(
      SELECT TOP(LEN(@m)/6) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) * 6 n
      FROM E4
    )  
    SELECT CAST( master.dbo.fn_cdc_hexstrtobin( '0x'
       + (
         SELECT SUBSTRING( @m, n-1, 2) + SUBSTRING( @m, n-3, 2)
         FROM cteTally
         FOR XML PATH(''),TYPE ).value('(./text())[1]','NVARCHAR(MAX)')) AS nvarchar(MAX)) AS STR_VAL;

    The function fn_cdc_hexstrtobin has a check for the 0x prefix so there is no need for adding the prefix.
    😎
    I don't like this function, it is an old relic that has an input limitation of NVARCHAR(40) and returns VARBINARY(10), here is an ITVFN alternative
    CREATE FUNCTION dbo.ITVFN_HEX_STR_TO_BIN
    (
      @HEX_STR  NVARCHAR(MAX)
    )
    RETURNS TABLE
    WITH SCHEMABINDING
    AS
    RETURN
    SELECT
      CONVERT
       (
        VARBINARY(MAX)
        ,CONCAT
          (
           CASE
            WHEN CHARINDEX(N'0x',@HEX_STR,1) = 1 THEN N''
            ELSE N'0x'
           END
           ,@HEX_STR
          )
        ,1
       ) AS BIN_VAL
    ;