Trim spaces, CR, LF from string in SQL Server

Question

I tried almost everything to remove some unnecessary characters from sql string but it's not working:

declare @string as char(20)='  my '
select @string as OriginalString
select len(Replace((@string),CHAR(10),''))as StrLen1;
SELECT len(REPLACE(REPLACE(@string, CHAR(13), ' '), CHAR(10), '')) as StrLen2

Output Screen

Need a way to get this done.

Answer 1

Of the three types of whitespace you seem to mention, space, newline, and carriage return, your @string only has spaces. But you never actually remove space. If you want to remove all three of these types of whitespace, you can try the following:

SELECT
    LEN(REPLACE(REPLACE(REPLACE(@string, CHAR(13), ''), CHAR(10), ''), ' ', '')) AS StrLen2

The output from the above query is 2, since after removing the whitespace we are left with the string 'my'.

Demo