Problem with trailing spaces on varchar field

Problem with trailing spaces on varchar field

Postby natanieljr » Mon 20 Sep 2010 15:55

I’m importing a text file to my database trough Delphi.

In my table there is a field:
DEENDERECO varchar(150)

I use a simple insert SQL to populate the table

insert into table (field1,…, DEENDERECO,…) values (…,’street 50 ‘,…)

Sometimes, this field is showed and printed as "street 50 " instead of "street 50" on my Delphi application.

Using a database viewer (IbExpert), the field is shown as "street 50", with no right blank characters.

When I do a copy/paste in this field and post the change, the problem disappears.

When I update another record, by SQL statement, the problem persists.

View the text file in binary mode, all blank characters are code 0x20.

I'm using both, Delphi 6 and 7, with dbExpIda 2.50.0.23

I have a database and a query sample.
natanieljr
 
Posts: 13
Joined: Fri 03 Sep 2010 14:33

Postby AndreyZ » Tue 21 Sep 2010 12:50

Hello,

DbxIda is showing varchar strings exactly as they are in database. If you want to trim the strings, then you can do it on the server side.
AndreyZ
 

Postby natanieljr » Mon 27 Sep 2010 11:54

The field is a VARCHAR, it does not store trailing spaces.

When I use IBExpert it shows correctly.

I made the following test:
the same database
the same source
changed the drivername from the connection to Interbase

Everything worked just fine.
natanieljr
 
Posts: 13
Joined: Fri 03 Sep 2010 14:33

Postby AndreyZ » Tue 28 Sep 2010 08:36

The behavoiur of the DbxIda driver is correct. I have tried to do the same insert SQL with the IBX components and the trailing spaces weren't trimmed. It seems that IBExpert trims the trailing spaces.
AndreyZ
 

Postby natanieljr » Tue 28 Sep 2010 12:59

Ok.

My doubt was because Borland's dbExpress driver seems to remove all the trailing spaces of the strings to.

But if this is the driver default behavior then I'll have to change the routine.

Thanks.
natanieljr
 
Posts: 13
Joined: Fri 03 Sep 2010 14:33

Postby Smoorfs » Tue 28 Feb 2012 12:57

the trailing spaces are probably no spaces (hex 20), they just look like one.

Try a CHAR2HEXINT(col) to see the actual hex value, e.g. could be '80' for LATIN.

And then use "trim (trailing '80'xc from col)"
Smoorfs
 
Posts: 2
Joined: Tue 28 Feb 2012 12:16


Return to dbExpress driver for InterBase & Firebird