question

ruancra avatar image
ruancra asked

Removing white space/line breaks from string

Hi guys I am quering a string where which seems to have a line break or whites pace in it, no matter what i try it cant be removed. I want to query only the word 'Balance', but 'Balance EXEC' gets returned. I copied the cell contents below: balance EXEC Have tried removing line breaks and trimming as below, but still no success. select replace(replace(rtrim(ltrim(splitdata)),CHAR(13),' '),CHAR(10), ' ') as string
tsqlsql2014
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CHAR(160) is a likely suspect, given things seen elsewhere...
3 Likes 3 ·
@ruancra Yes, this is expected. If this question in any way related to the one you [asked on May 9][1], you may notice that in the answer the line break is replaced with single space, not empty string, To make your solution work you might want to consider this: When replacing characters, opt to replace tab (char(9)), line feed (char(10)) and non-breaking space (char(160)) with single space. Others still need to be replaced with empoty string. This will yield the result when you have 2 or more spaces between words, but the good news is that you will know that these are just spaces. In order to address this situation, please refer to the very cool solution published by @Jeff Moden. The article is titled [REPLACE Multiple Spaces with One][2]. Once you apply Jeff's solution to what you have after replacing the characters, you will get the results you need. [1]: https://ask.sqlservercentral.com/questions/142969/count-and-display-words-from-a-string.html [2]: http://www.sqlservercentral.com/articles/T-SQL/68378/
2 Likes 2 ·
Plus 1 to Thomas' CHAR(160) comment above.
1 Like 1 ·
@ruancra This specific character can travel to the varchar values in SQL Server via web/html text as it is very common to use the non-breaking space there (which is char(160), represented in html as \nbsp;). Another candidate is the tab character (char(9)). I think that @ThomasRushton comment should be converted to answer.
0 Likes 0 ·
Thanks, I tried adding char(160): char(160),replace(replace(replace(replace(rtrim(ltrim(splitdata)),CHAR(9),''),CHAR(13),''),CHAR(10), ''),CHAR(160),'') as FinalString it now returns 'balnceEXEC'. There is a space directly after the 7th character when doing a substring.
0 Likes 0 ·
Show more comments

1 Answer

·
BUD avatar image
BUD answered

Replace need to be nested

replace(REPLACE(dbo.[Comment], CHAR(10),''), CHAR(13),'') as comment

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.