Can't trim white space from large varchar field

[fig000]

Hi,

I am reading two very large varchar fields (2600) from the sql
server 2005 database of our ERP. This reading is being done by a
cursor. In the cursor select statement I rtrim and ltrim the field. No
matter what I do the length of the field is always 2600. I can see
that there is some sort of white space to the right of the text.
Whatever that white space is, it can't be trimmed by ltrim or rtrim.

I tried running the sql from the cursor as a query and had the
same problem.

Any help would be appreciate it.

fig000

[Tom Cooper]

If you do a RTrim of a varchar column, the result will not have trailing
blanks. However, it might have characters that display as blanks, (for
example a tab character, but there are other possibilities). If you do a

SELECT CAST(RIGHT(RTRIM(<your column>), 10) As varbinary(10))

that will show you the rightmost 10 characters in hex (you'll get something
like 0x62636465666768202009). The last two hex digits (09 in this case) is
the rightmost character after the RTRIM. It would be 20 if it is a blank,
if it is anything else, the rightmost character is not a blank.

Tom

"fig000" <strin...@gmail.com> wrote in message
news:a3ffe5f0-8db5-452d...@g17g2000yqe.googlegroups.com...

[fig000]

Tom,

It had occurred to me that there might be bytes other than space (20
hex/23 ascii) in what looks like the whitespace in the field. I did
some experiments using the ascii funciton and found that the white
space is actually filled with ascii 0 or null. I'm not sure I can use
the replace function since there is no alpha representation for 0. Any
suggestions? I can see writing a function to go byte by byte through
the field value and change 0 to 32 so I could them use rtrim to get
rid of the spaces.

I hope there's a better way.

Neil

On Sep 3, 1:32 am, "Tom Cooper" <tomcoo...@comcast.net> wrote:
> If you do a RTrim of a varchar column, the result will not have trailing
> blanks.  However, it might have characters that display as blanks, (for
> example a tab character, but there are other possibilities).  If you do a
>
> SELECT CAST(RIGHT(RTRIM(<your column>), 10) As varbinary(10))
>
> that will show you the rightmost 10 characters in hex (you'll get something
> like 0x62636465666768202009).  The last two hex digits (09 in this case) is
> the rightmost character after the RTRIM.  It would be 20 if it is a blank,
> if it is anything else, the rightmost character is not a blank.
>
> Tom
>

> "fig000" <stringma...@gmail.com> wrote in message

>
> news:a3ffe5f0-8db5-452d...@g17g2000yqe.googlegroups.com...
>
>
>
> > Hi,
>
> >    I am reading two very large varchar fields (2600) from the sql
> > server 2005 database of our ERP. This reading is being done by a
> > cursor. In the cursor select statement I rtrim and ltrim the field. No
> > matter what I do the length of the field is always 2600. I can see
> > that there is some sort of white space to the right of the text.
> > Whatever that white space is, it can't be trimmed by ltrim or rtrim.
>
> >    I tried running the sql from the cursor as a query and had the
> > same problem.
>
> >     Any help would be appreciate it.
>

> > fig000- Hide quoted text -
>
> - Show quoted text -

[Tom Cooper]

You can use Replace() function. Because you can get a character will any
possible Ascii value by using the CHAR() function. For example, the
following code removes all Ascii 0's.

Declare @Test varchar(30)
Set @Test = 'ABC' + Replicate(Char(0), 5);
/* Notice that @Test has 5 hex 00's */
Select 'Before', Cast(@Test As varbinary(30))
Set @Test = Replace(@Test, Char(0), '');
/* The hex 00's are now gone */
Select 'After', Cast(@Test As varbinary(30));

Tom

P.S., you probably already know this, but even though Ascii zero is
sometimes called null (you will often see it as the NUL character in ASCII
charts) and some computer languages use it to mean special things (like the
end of a variable length string), it is very different than the SQL NULL
value. That is
Set @X = Char(0)
and
Set @X = NULL
are not the same.

"fig000" <strin...@gmail.com> wrote in message

news:52ecc200-d3c0-4363...@w15g2000pro.googlegroups.com...

[fig000]

For those of you with the same problem, this seems to work to replace
the null (0) bytes in the field with spaces.

rtrim(ltrim(REPLACE(lt.ExtendedText,char(0),char(32))))

Any better ideas would be appreciated.

Thanks for all the help.

On Sep 3, 11:57 am, "Tom Cooper" <tomcoo...@comcast.net> wrote:
> You can use Replace() function.  Because you can get a character will any
> possible Ascii value by using the CHAR() function.  For example, the
> following code removes all Ascii 0's.
>
> Declare @Test varchar(30)
> Set @Test = 'ABC' + Replicate(Char(0), 5);
> /* Notice that @Test has 5 hex 00's */
> Select 'Before', Cast(@Test As varbinary(30))
> Set @Test = Replace(@Test, Char(0), '');
> /* The hex 00's are now gone */
> Select 'After', Cast(@Test As varbinary(30));
>
> Tom
>
> P.S., you probably already know this, but even though Ascii zero is
> sometimes called null (you will often see it as the NUL character in ASCII
> charts) and some computer languages use it to mean special things (like the
> end of a variable length string), it is very different than the SQL NULL
> value.  That is
> Set @X = Char(0)
> and
> Set @X = NULL
> are not the same.
>

> > - Show quoted text -- Hide quoted text -

[Erland Sommarskog]

Tom Cooper (tomc...@comcast.net) writes:
> You can use Replace() function. Because you can get a character will any
> possible Ascii value by using the CHAR() function. For example, the
> following code removes all Ascii 0's.
>
> Declare @Test varchar(30)
> Set @Test = 'ABC' + Replicate(Char(0), 5);
> /* Notice that @Test has 5 hex 00's */
> Select 'Before', Cast(@Test As varbinary(30))
> Set @Test = Replace(@Test, Char(0), '');
> /* The hex 00's are now gone */
> Select 'After', Cast(@Test As varbinary(30));

Make that

Set @Test = Replace(@Test COLLATE Latin1_General_BIN2, Char(0), '');

I guess you use an SQL collation, in which case the above works. But
I use a Windows collation, and with a Windows collation replace does
not work with char(0), because it's an undefined character in Unicode.
Whence you need to cast a binary collation or an SQL collation.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Tom Cooper]

You are correct. Thanks.
And you're also correct, I'm still running SQL_Latin1_General_CP1_CI_AS in
most places. It's on my list of things to do to get everything over to a
windows collation, but it hasn't happened yer.

Tom

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9DE8E757B...@127.0.0.1...