Hi All,
I have a unique requirement.
I have data coming in as 000232,00116
I have to capture the number of leading zeros and along with the 1st occurance of the number after the leading zeros.
Eg: i have data coming in as 000232.
in my output i should be able to see 42 (4 is the starting occurance after after leading zero and 2 is the 1st number after leading zero)
Eg-2: in coming data is: 00116
output data: 31 (3 is the 1st occurance of a number after leading zeros and 1 is the 1st number after leading zero)
anyone please give me suggestions.
Thanks & Regards
Mark
Finding the number of leading zeros
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In my experience nearly all "unique requirements" of this nature are best solved with a custom routine. However, what you need can be done with (for example) stage variable expressions.
To get the location of the first non-zero character, trim the leading zeroes and subtract the length of that from the length of the original string.
To get the second digit you require, simply take the leftmost character from the trimmed string.
To get the location of the first non-zero character, trim the leading zeroes and subtract the length of that from the length of the original string.
To get the second digit you require, simply take the leftmost character from the trimmed string.
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.
Could you also use something like:
to get the first required digit then use the field function with the newly acquired digit as your starting point to select 1 char?
Would this carry more processing overhead?
Addendum: Actually this won't work at all, you would have to do 9 index() statements (I think)
Code: Select all
Index("1,2,3,4,5,6,7,8,9",InputCol.String,1)
Would this carry more processing overhead?
Addendum: Actually this won't work at all, you would have to do 9 index() statements (I think)
Last edited by ShaneMuir on Tue Mar 18, 2008 5:03 pm, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I think so. Len() is probably the most efficient function in DataStage, because the length of a (variable length) string is stored as that string's prefix. You find the length even before you find the string!
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.
I'd use the LEN() function as well. You know how long the numeric column is, when you convert from number to string and use LEN() and LEFT() functions to parse your result.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>