CODEWITHSUNDEEP
sql-server
user8237783
user8237783

Reputation:

Trim spaces, CR, LF from string in SQL Server

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.

Upvotes: 4

Views: 14148

Answers (5)

Sharon Rimer
Sharon Rimer

Reputation: 166

From SQL Server 2017 (14.x) and later

declare @string as char(20)='  my ';
SELECT TRIM(CHAR(9) + CHAR(10) + CHAR(13) + CHAR(32) FROM @string); --Tab, Line Break, Carriage Return, White Space

Upvotes: 0

ToanTV
ToanTV

Reputation: 109

declare @newline char(2)  
set @newline= char(13)+char(10)   --  CR + LF

update **Your Table Here** 

-- ItemCode is column Name,Find and replace values CRLF = ''
Set ItemCode=REPLACE(ItemCode,@newline,'')   
where RIGHT(ItemCode,2) = @newline   -- Maybe no need

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

If you're trying to trim all spaces try this : 

Select RTRIM(LTRIM(@string))

DEMO

Upvotes: -1

cloudsafe
cloudsafe

Reputation: 2506

If you just want to get rid of spaces:

declare @string as char(20)='  my '
select LTRIM(RTRIM(@string))

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

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

Upvotes: 6

Related Questions