June 19, 2017 at 5:58 am
farax_x - Monday, June 19, 2017 4:57 AMHi 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ɹʄɳʅ
June 19, 2017 at 12:40 pm
Eirikur Eiriksson - Monday, June 19, 2017 5:58 AMfarax_x - Monday, June 19, 2017 4:57 AMHi 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_VALOutput
ɹʄɳʅ
the out put is not correct the right one is سلام
June 19, 2017 at 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;
June 20, 2017 at 2:01 am
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????
June 20, 2017 at 3:52 am
Luis Cazares - Monday, June 19, 2017 1:22 PMI 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 alternativeCREATE 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
;
June 20, 2017 at 6:19 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply