Anyone? ASCII_VAL() shows the data ends with spaces, yet RTRIM() isn't removing them.

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Tuesday, May 09, 2006 12:04 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] LTRIM and RTRIM failures

Can anyone explain why LTRIM and RTRIM aren't trimming ASCII 32 characters? Some data has gotten messed up with trailing blanks.

Let's see if any data has leading or trailing spaces select * from rpl_po po where strlen(po.PO) <> strlen(ltrim(rtrim(po.PO)))
Results: empty set

Let's spot check a particular item to see what it's last character is select ascii_val(substring(po.PO from 9 for 1)) from RPL_PO po where po.ID = 3144
Results: 32

Let's test that particular item again
Select strlen(po.PO), strlen(ltrim(rtrim(po.PO))) from RPL_PO po where po.ID = 3144
Results: 11, 11

Hmmm, let me put quotes around it in order to see leading or trailing characters select '''' || po.PO || '''' from rpl_po po where po.ID = 3144
Results: '2895-01V   '

Let's test LTRIM and RTRIM and see what they return select strlen(ltrim(rtrim(' xxx '))), ascii_val(substring(' xxx ' from 5 for 1)) from rdb$database
Results: 3, 32

Here's the table that we are using

RECREATE TABLE RPL_PO
(
ID BIGINT NOT NULL,
PO VARCHAR(16) NOT NULL,
/* snip */
CONSTRAINT PK_RPL_PO PRIMARY KEY (ID)
);


------------------------ Yahoo! Groups Sponsor --------------------~--> Everything you need is one click away.  Make Yahoo! your home page now.
http://us.click.yahoo.com/AHchtC/4FxNAA/yQLSAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links