There is a TRIM() function, but you don't normally want to use it on a
column in the WHERE clause. Any function around the column name in a
comparison prevents Oracle from using any indexes that may exist on that
column to resolve the comparison.
If you are sending a literal ('1001') to Oracle, it is supposed to be
automagically padded with the correct number of spaces for the comparison.
If you are using bind variables (as you should), trailing spaces tend to get
trimmed. You can avoid the problem by setting database handle attribute
ora_ph_type to 96 before preparing the statement:
# This requires DBD::Oracle 1.06 or later
$dbh -> {ora_ph_type} = 96; # Tell DBD::Oracle not to trim placeholders
Unfortunately, the attribute is not well documented yet.
Personally, I wouldn't use CHAR columns except under extreme duress or for
very specific purposes.
--
Mac :})
** I normally forward private database questions to the DBI mail lists. **
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Steve Howard" <[EMAIL PROTECTED]>
To: "Guru Prasad" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, February 27, 2001 5:09 AM
Subject: RE: Problem with CHAR Data Type in ORACLE
> It sounds like the trailing blanks are causing it to not match. I know MS
> SQL has a rtrim() function. do you have anything comparable in Oracle?
(I'm
> sure you do). If so, and this is the problem, try something like:
>
> SELECT RTRIM(name) FROM junk WHERE RTRIM(id)='1001'
>
> (That's MS Syntax, but I'm sure Oracle will have a similar way of doing
it.)
