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.

Ryan Chong

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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jknj72

ASKER

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

I meant I have a field that I set to a variable

slightwv (䄆 Netminder)

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:
https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_indexes.htm#ADFNS00505

slightwv (䄆 Netminder)

>>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   ');
commit;

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

Open in new window

johnsone

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.

jknj72

ASKER

thanks