Unable to trim leading zeroes using trim()

ArndW · Post by **ArndW** » Fri Feb 10, 2006 7:54 am

I just wrote a 60-second PX job. Starts with a row generator that generates 100 rows with just one column called "Test" of type VarChar(32). The "edit row" sets the values to cycle between "00001" and "00000002". The transform does a simple "TRIM(DSLink2.Test,'0','L')" and the output goes to a PEEK stage. The job runs and the peek output shows that the leading '0' characters have been trimmed.

Are you using NLS in your project and could your zeroes be another glyph in the character space? You could add a SEQ(Test[1,1]) to your output to see if the returned value is 32 as expected.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 10, 2006 2:37 pm

Umm... 32 is Seq(" "), not Seq("0")

ArndW · Post by **ArndW** » Fri Feb 10, 2006 3:24 pm

Yep - thanks for the correction.

kumar_s · Post by **kumar_s** » Sat Feb 11, 2006 2:43 am

Hmm.. I am not quite sure, NLS is going to be an issue. because Trim function is used with '0' and not with the character which returns 48 [Seq('0') = 48]

-Kumar

ArndW · Post by **ArndW** » Sat Feb 11, 2006 4:21 am

Kumar - I don't understand what your post means. The point of my question was to make sure that the string contains a "0" character as we expect it - namely one which has ascii value of 48. If the string contains another glyph then then you would need to change the TRIM function to remove that. All the people here who have tested the TRIM() function have done so successfully, so the attempt is to look for some other explanation. My NLS conjecture is a stab in the dark.

kumar_s · Post by **kumar_s** » Sat Feb 11, 2006 4:39 am

ArndW wrote:Kumar - I don't understand what your post means. The point of my question was to make sure that the string contains a "0" character as we expect it - namely one which has ascii value of 48. If the string contains another glyph then then you would need to change the TRIM function to remove that. All the people here who have tested the
TRIM() function have done so successfully, so the attempt is to look for some other explanation. My NLS conjecture is a stab in the dark.

Poster was not able to perform Trim('0001','0','L').
If seq('0') was not equal to 48, then value (0) in the '0001' should not also be equal to 48. ie., both underlying unicode should be equal (atlest with respect to datastage). For example, if seq('0') returns 50 unexpectedly, the '0' in '0001' should also return the same. And so, should trim. So i was under the impression that it could be of some other issue. Sorry if if i got it wrongly

-Kumar

ArndW · Post by **ArndW** » Sat Feb 11, 2006 5:22 am

Kumar - I see what you mean; I went back and saw that the poster did use a constant text, not data. So it really ought to have worked, regardless of any NLS setting!

alisoviejo · Post by **alisoviejo** » Mon Feb 13, 2006 1:53 pm

I had a smilar issue and had to use ltrim inthe Oracle stage (Source) !

jmessiha · Post by **jmessiha** » Mon Feb 13, 2006 3:48 pm

Yeah, what a pain. Thats why I ended up using the oconvert, trimf, oconvert again. I still don't know why the Trim is not working as described.

alisoviejo wrote:I had a smilar issue and had to use ltrim inthe Oracle stage (Source) !

rasi · Post by **rasi** » Mon Feb 13, 2006 11:19 pm

Have you reported to Ascential as BUG. Open a case with Ascential and they might be able to sort out this issue.

somu_june · Post by **somu_june** » Tue Feb 14, 2006 7:16 pm

Hi,

Iam facing a fatal error when Iam trimming white spaces. I have source file text file with material char(7) as a columm and Iam having a OSP in DB2 table with Varchar(7) as a key. Iam looking with Material to OSP . I used functions to trim whitespaces after material and I also had two Leading 00 before material.

Ex 00S0003X

I used TrimLeadingTrailing(Frm_src.Material) and I also tried with StripWhitespace(frm_src.Material). Iam getting an error showing....

main_program: Syntax error: Error in "lookup" operator: Error in input redirection: Error in input parameters: Error in view adapter: Error in binding: Expected '=', got: ")", line 43
Expected ';' or ']', got: "=", line 43; text: "=", line 43 [view()
Expected option or identifier, got: ")", line 44; text: [view()=OSP;

2. I have another doubt . In lookup stage. for Lookup key MANDT. Iam assigning Key expression as job parameter can I give like that

for ex :

lkuptable

Keyexpression . colummname
bbbb(from job parameter) . mandt

Thanks,
Somaraju

ray.wurlod · Post by **ray.wurlod** » Tue Feb 14, 2006 7:29 pm

Please don't hijack threads. Neither of your questions relates to trimming leading zeroes from strings in parallel jobs. Please start a separate thread for each, to assist future searchers.