Trim the data

deva · Post by **deva** » Wed Jan 16, 2008 1:41 pm

Hi,
I am loading the data into database. I have one column in target table. Its length is varchar(40), some times the Incomming data is more than 40 charactors. so those records are rejecting.

I am using trim() to remove leading charactors. But the trim is not working, what is the reason? records are rejecting even If we use trimB or Trim.

Can any one suggest on this.

Thanks in advanse

ray.wurlod · Post by **ray.wurlod** » Wed Jan 16, 2008 1:50 pm

What are the "leading characters" that you want to trim? What syntax of the Trim() function you are using? TrimB() trims trailing white space characters, not leading - you need TrimF() for leading white space. Do your data in fact have leading white space?

Further, if you have 45 non-white-space characters followed or led by zero or more white space characters, Trim() will leave 45 characters.

You probably need a Left() function or substring operator, possibly in conjunction with Trim(), to achieve the effect you require; that is, to limit the output to not more than 40 characters.

mohandl · Post by **mohandl** » Thu Jan 17, 2008 12:51 am

I think we should have to use Left() function to truncate to 40 char.
Trim function is used to eliminate Spaces

Regards,
Mohan

mohandl · Post by **mohandl** » Thu Jan 17, 2008 12:51 am

I think we should have to use Left() function to truncate to 40 char.
Trim function is used to eliminate Spaces

Regards,
Mohan

dhanashreepanse · Post by **dhanashreepanse** » Thu Jan 17, 2008 3:30 am

You can try Trim(Left(incoming column,40)).

us1aslam1us · Post by **us1aslam1us** » Thu Jan 17, 2008 4:17 am

First trim the data then use left() to truncate it. But as ray suggested, check what those characters are before even going with the above rule.