CODEWITHSUNDEEP
sql-servert-sql
Mike S
Mike S

Reputation: 306

TSQL: tab(non print character) removal, something is wrong

I got this issue while doing fixed length file formatting which was done with replicate(' ',n). Later I switched to underscore just to make sure it not that spaces. And after that I got some tab at the end of one field which I can not fight, once placed in notepad (any type, any encoding) or other editor it adds extra 4b. I narrowed this problem to this test code, tried on all our MS servers which have default settings, So now if I paste v2 into notepad I got again those extra bytes, same thing if I do export. I see that replace remove 1 b but still something else there, I also tried char(13) + char(10). I have v2 = 60 just in case if html will eat something. Tx much all.

DECLARE @vv VARCHAR(1000) = 'Alpha Bravo Charlie_________________________________________   ';
DECLARE @vv2 VARCHAR(1000) = REPLACE(@vv,CHAR(9),'');
SELECT @vv v1,  LEN(@vv) len1, CHARINDEX(@vv,CHAR(9)) i9, '--' [-->], @vv2 v2, LEN(@vv2) len2

Upvotes: 0

Views: 62

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

If it helps, here is a UDF to strip control characters

CREATE FUNCTION [dbo].[udf-Str-Strip-Control](@S varchar(max))
Returns varchar(max)
Begin
    ;with  cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(C) As (Select Top (32) Char(Row_Number() over (Order By (Select NULL))-1) From cte1 a,cte1 b)
    Select @S = Replace(@S,C,' ')
     From  cte2

    Return LTrim(RTrim(Replace(Replace(@S,'   ',' '),'  ',' ')))
End
--Select [dbo].[udf-Str-Strip-Control]('Michael'+char(13)+char(10)+'LastName')  --Returns: Michael LastName

Upvotes: 1

Related Questions