June 19, 2017 at 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 ?
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 suggestionDECLARE @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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy