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

more ▼

asked May 19 at 11:48 AM in Default

avatar image

1.2k 31 36 45

CHAR(160) is a likely suspect, given things seen elsewhere...

May 19 at 02:20 PM ThomasRushton ♦♦

Plus 1 to Thomas' CHAR(160) comment above.

May 19 at 11:47 PM Jeff Moden

@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.

May 21 at 01:22 AM Oleg

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.

May 22 at 10:51 AM ruancra

@ruancra Yes, this is expected. If this question in any way related to the one you asked on May 9, 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. Once you apply Jeff's solution to what you have after replacing the characters, you will get the results you need.

May 22 at 01:22 PM Oleg
show all comments (comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 19 at 11:48 AM

Seen: 70 times

Last Updated: May 25 at 02:49 PM

Copyright 2016 Redgate Software. Privacy Policy