Link to home
Start Free TrialLog in
Avatar of jknj72

asked on

Trim functionality

if I have a variable VARCHAR2(18 BYTE) and occasionally the string has empty space(ie, 'W12345678901      ' ) and after setting it to a variable, I use the variable equal to a field from a table in a query. Will it equal the string with the empty spaces? I am noticing  in certain situations that it isn't? Don't have specifics but can come up with some if needed. Just wanted a little insight to how this works exactly?
I have  since used TRIM on my setting of the variable. I was using TRIM in the query but I  think its taking longer especially when looping through a big record set.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

it's always be advised to NOT saving extra space into database.

if the field already hav values with extra space, u may want to do a data cleaning to maintain the data accuracy and integrity.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jknj72


>>if I have a variable VARCHAR2(18 BYTE)

I meant I have a field that I set to a variable
I just thought of something:

TRIM, or any function on an indexed column will make the index unusable.

An explain plan will confirm this.

If you need the trim and the index, you need to create a Function Based Index:
>>I meant I have a field that I set to a variable

Oracle doesn't automatically trim spaces.  If you insert them, then stay...

drop table tab1 purge;
create table tab1(col1 varchar2(10));

insert into tab1 values('a   ');

select ':' || col1 || ':' from tab1;

Open in new window

For cases like this, we usually put triggers in place to trim the value when it is inserted/updated.  That way there are no issues.  A few lines in a trigger and a one time update and your problem goes away.
Avatar of jknj72