question

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

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

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

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

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.