Jeff Moden (11/15/2016)
Eirikur Eiriksson (11/15/2016)
Here is a quick solution, quite efficient😎
DECLARE @pString VARCHAR(8000) = 'Chlu' + CHAR(30) + CHAR(2) + CHAR(0) + CHAR(13) + CHAR(10) + CHAR(20) + CHAR(31) + 'emcká';;WITH T(N) AS ( SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))X(N))
, NUMS(N) AS ( SELECT TOP (LEN(ISNULL(@pString,CHAR(32)))) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM T T1,T T2,T T3,T T4)
SELECT
(
SELECT SUBSTRING(@pString,NM.N,1)
FROM NUMS NM
WHERE ((ASCII(SUBSTRING(@pString,NM.N,1)) - 32) & 0x7FFF) < 256
FOR XML PATH('')
) AS OUT_STR
;
Try this and see what you get...
DECLARE @pString VARCHAR(8000) = 'Chlu' + CHAR(30) + CHAR(2) + CHAR(0) + CHAR(13) + '&' + CHAR(10) + CHAR(20) + CHAR(31) + 'emcká';;WITH T(N) AS ( SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))X(N))
, NUMS(N) AS ( SELECT TOP (LEN(ISNULL(@pString,CHAR(32)))) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM T T1,T T2,T T3,T T4)
SELECT
(
SELECT SUBSTRING(@pString,NM.N,1)
FROM NUMS NM
WHERE ((ASCII(SUBSTRING(@pString,NM.N,1)) - 32) & 0x7FFF) < 256
FOR XML PATH('')
) AS OUT_STR
;
You need to use "TYPE" and that will make it about twice as slow.
One would use the text() function to mitigate the cost, makes a big difference by avoiding the XML reconstruction phase of the output.
😎
The cost ratio between the first and the second query is close to 1:99
DECLARE @pString VARCHAR(8000) = 'Chlu' + CHAR(30) + CHAR(2) + CHAR(0) + CHAR(13) + '&' + CHAR(10) + CHAR(20) + CHAR(31) + 'emcká';
--Better
;WITH T(N) AS ( SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))X(N))
, NUMS(N) AS ( SELECT TOP (LEN(ISNULL(@pString,CHAR(32)))) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM T T1,T T2,T T3,T T4)
SELECT
(
SELECT SUBSTRING(@pString,NM.N,1)
FROM NUMS NM
WHERE ((ASCII(SUBSTRING(@pString,NM.N,1)) - 32) & 0x7FFF) < 256
FOR XML PATH(''), TYPE
).value('(./text())[1]','VARCHAR(8000)') AS OUT_STR
;
-- Bad
;WITH T(N) AS ( SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))X(N))
, NUMS(N) AS ( SELECT TOP (LEN(ISNULL(@pString,CHAR(32)))) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM T T1,T T2,T T3,T T4)
SELECT
(
SELECT SUBSTRING(@pString,NM.N,1)
FROM NUMS NM
WHERE ((ASCII(SUBSTRING(@pString,NM.N,1)) - 32) & 0x7FFF) < 256
FOR XML PATH(''), TYPE
).value('(.)[1]','VARCHAR(8000)') AS OUT_STR
;