Syntax for substring

ambasta · Post by **ambasta** » Tue Oct 03, 2006 5:10 am

Can anyone please help me getting syntax for substring.
My requirement is...
I have a column having data like 0000000000012345678(length is 19)and i want to populate it to the target side as 00000012345678(length is 14).how to do it??
is there any inbuilt function in parallel job???

Regards,
ambasta

ArndW · Post by **ArndW** » Tue Oct 03, 2006 5:14 am

Yes, you can use the syntax of In.ColumnName[6,14] or even a RIGHT(In.ColumnName,14).

Kirtikumar · Post by **Kirtikumar** » Tue Oct 03, 2006 5:16 am

Is the length of your input is always fixed i.e. all 19 chars will have value?
If yes, then just say InCol[6,14] and it should work.

ambasta · Post by **ambasta** » Tue Oct 03, 2006 6:22 am

Thanx a lot your kind suggessions,but,unfortunately these functions are not working for me.Somehow i made this work,but i don't know how it is working.
Ex..if i need last 14 element out of 19 then IN.Col[16,14] is working and if i need last 12 element out of 19 then InCol[18,12] is working.
Can anyone help me???

ambasta · Post by **ambasta** » Tue Oct 03, 2006 6:23 am

Thanx a lot your kind suggessions,but,unfortunately these functions are not working for me.Somehow i made this work,but i don't know how it is working.
Ex..if i need last 14 element out of 19 then IN.Col[16,14] is working and if i need last 12 element out of 19 then InCol[18,12] is working.
Can anyone help me???

ambasta · Post by **ambasta** » Tue Oct 03, 2006 6:24 am

Thanx a lot for your kind suggessions,but,unfortunately these functions are not working for me.Somehow i made this work,but i don't know how it is working.
Ex..if i need last 14 element out of 19 then IN.Col[16,14] is working and if i need last 12 element out of 19 then InCol[18,12] is working.
Can anyone help me???

ambasta · Post by **ambasta** » Tue Oct 03, 2006 6:25 am

Thanx a lot for your kind suggessions,but,unfortunately these functions are not working for me.Somehow i made this work,but i don't know how it is working.
Ex..if i need last 14 element out of 19 then IN.Col[16,14] is working and if i need last 12 element out of 19 then InCol[18,12] is working.
Can anyone help me???

thompsonp · Post by **thompsonp** » Tue Oct 03, 2006 6:27 am

Looks like you need to check the datatype and length of the input field you are trying to substring.

The behaviour you describe suggests the field is 30 characters long.

Run the job with $OSH_PRINT_SCHEMAS set to true and examine what the datatypes really are.

thompsonp · Post by **thompsonp** » Tue Oct 03, 2006 6:29 am

spot the mistake... that would 29 characters long

ambasta · Post by **ambasta** » Tue Oct 03, 2006 6:35 am

Actually the incoming column's dataype is Decimal(19,4) and target datatype is char(15) as i am writing to file.My requirement is to multiply input column by 10,000 and then populate it to fixed width file.this is the actual data that i am getting 000000000000019.0000 and my requirement is to populate it as 000000000190000

ray.wurlod · Post by **ray.wurlod** » Tue Oct 03, 2006 3:48 pm

Perform the calculation, then convert the decimal number to a string. You can use the Right() function to extract the relevant number of characters from the right-hand end.