Convert unicode escape sequence to nvarchar

• farax_x

  Hall of Fame

  Points: 3579

  More actions

  June 19, 2017 at 4:57 am

  #343286

  Hi all,
  I have string like this:
  
DECLARE @m NVARCHAR(max)= N'\u0633\u0644\u0627\u0645'

  How can I convert(Unescape) it to normal string ?

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  June 19, 2017 at 5:58 am

  #1947233

  farax_x - Monday, June 19, 2017 4:57 AM

  Hi all,
  I have string like this:
  
DECLARE @m NVARCHAR(max)= N'\u0633\u0644\u0627\u0645'

  How can I convert(Unescape) it to normal string ?

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

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(LEN(@m) / 6) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
  (
  SELECT
   N'' + NCHAR(CONVERT(INT,SUBSTRING(@m,((NM.N * 6) - 5) + 2,4),0))
  FROM NUMS NM
  FOR XML PATH(''),TYPE
  ).value('(./text())[1]','NVARCHAR(MAX)') AS STR_VAL

  Output
  ɹʄɳʅ

• farax_x

  Hall of Fame

  Points: 3579

  More actions

  June 19, 2017 at 12:40 pm

  #1947306

  Eirikur Eiriksson - Monday, June 19, 2017 5:58 AM

  farax_x - Monday, June 19, 2017 4:57 AM

  Hi all,
  I have string like this:
  
DECLARE @m NVARCHAR(max)= N'\u0633\u0644\u0627\u0645'

  How can I convert(Unescape) it to normal string ?

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

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(LEN(@m) / 6) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
  (
  SELECT
   N'' + NCHAR(CONVERT(INT,SUBSTRING(@m,((NM.N * 6) - 5) + 2,4),0))
  FROM NUMS NM
  FOR XML PATH(''),TYPE
  ).value('(./text())[1]','NVARCHAR(MAX)') AS STR_VAL

  Output
  ɹʄɳʅ

  the out put is not correct the right one is 
  سلام

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 19, 2017 at 1:22 pm

  #1947310

  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;


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  June 20, 2017 at 2:01 am

  #1947342

  Corrected version without a scalar function
  

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

  ;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP(LEN(@m) / 6) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
(
  SELECT
   N'' + NCHAR(CONVERT(INT,CONVERT(VARBINARY(MAX),CONCAT(N'0x',SUBSTRING(@m,((NM.N * 6) - 5) + 2,4)),1),0))
  FROM NUMS NM
  FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)') AS STR_VAL

  Output
  ????

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  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
;


• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 20, 2017 at 6:19 am

  #1947366

  That's great Eirikur.
  I wasn't in the mood of creating a new string to hex function (not much use on my side), so I used what was available.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2