Removing white spaces

[i***@gmail.com]

I am trying to create a txt file from sql server 2008 database which we have to send to the bank every night. I have tried rtrim, ltrim, replace but for some reason the data still contains empty spaces.
I also wrote the following sql function but this one also doesnt remove spaces.

CREATE FUNCTION [dbo].[fnRemoveAllSpaces]
(
@InputString VARCHAR(max)
)
RETURNS VARCHAR(max)
AS
BEGIN
--WHILE CHARINDEX(' ',@InputString) > 0 -- Checking for the spaces
SET @InputString =
LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(
@InputString
,char(0),'')
,char(1),'')
,char(2),'')
,char(3),'')
,char(4),'')
,char(5),'')
,char(6),'')
,char(7),'')
,char(8),'')
,char(9),'')
,char(10),'')
,char(11),'')
,char(12),'')
,char(13),'')
,char(14),'')
,char(15),'')
,char(16),'')
,char(17),'')
,char(18),'')
,char(18),'')
,char(20),'')
,char(21),'')
,char(22),'')
,char(23),'')
,char(24),'')
,char(25),'')
,char(26),'')
,char(27),'')
,char(28),'')
,char(29),'')
,char(30),'')
,char(31),'')
,' ','')
))
RETURN @InputString
END

[Erland Sommarskog]

Post by i***@gmail.com
I am trying to create a txt file from sql server 2008 database which we
have to send to the bank every night. I have tried rtrim, ltrim, replace
but for some reason the data still contains empty spaces.
I also wrote the following sql function but this one also doesnt remove spaces.

For the function to work, you need to cast the input data to a binary
collation:

@InputString COLLATE Latin1_Generai_BIN2

This is because, many of the code points are undefined in Unicode, why
replace does not care about them.

However, the most likely culprit is char(160), no-break-space. But you
need to cast the string to binary to see what those space-looking characters
actually are.

You should probably remove most of those control characters from your
function, because they are unlikely to be the problem, and looking
for all of them is unnecessarily expensive. You should keep 9, 10, 12 and
13.