unable to trim
Moderators: chulett, rschirm, roy
unable to trim
Hi,
A dataset is created using the $APT_STRING_PADCHAr(0x20).
when inserting to a table, the data gets inserted along with the space.
Use of trim function has not so far helped to remove the space inspite of the data type in oracle being a varchar.
Is there any other means of doing this?
Pls let me know.
Thanks in advance!!
A dataset is created using the $APT_STRING_PADCHAr(0x20).
when inserting to a table, the data gets inserted along with the space.
Use of trim function has not so far helped to remove the space inspite of the data type in oracle being a varchar.
Is there any other means of doing this?
Pls let me know.
Thanks in advance!!
Regards,
Madhu Dharmapuri
Madhu Dharmapuri
The trim function will always remove spaces from strings, so either you don't have spaces but other characters or you don't have strings - note, you cannot remove trailing spaces from a CHAR field, just from VARCHAR. What are your datatypes in the job and the table? Are you doing a TRIM() on CHAR field and later implicilty converting to VARCHAR?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
What is the datatype from your dataset schema? Add a 2nd output link to your transform going into a peek stage with your TRIM() string and add a column of LEN(trimmed-string) and see if the results are as expected.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
You cannot TRIM a char field. It has fixed width and will always blank pad to the fixed length. You need to declare a VARCHAR column datatype and then TRIM your original CHAR column into that.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Pedantically you can TRIM a CHAR data type, and it is immedately padded again with APT_STRING_PADCHAR (automatically, because its data type is CHAR).
The effect is the same.
The effect is the same.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
This is the first mention of a varchar RHS column. If you explicitly TRIM that column and output it to a peek stage are the spaces gone? And then they re-appear when writing to your database? What database are you using?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Siebel is the CRM system sitting on top of a database - probably DB2 or Oracle. And what happens in the PEEK stage with the trimmed field?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
So the RHS column is trimmed correctly, and that is what you are passing on to your output stage where the blanks are being added in again? This would only happen if you are going to a fixed width CHAR field - are you sure you aren't doing that somewhere? Are you using user-defined SQL and, if so, are you doing any conversions there?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>