Finding the number of leading zeros

[pavan_test]

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

[ray.wurlod]

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.

[chulett]

Nice. Had pondered this briefly today and hadn't come up with anything.

[ShaneMuir]

Could you also use something like:

Code: Select all

Index("1,2,3,4,5,6,7,8,9",InputCol.String,1)

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)

[ray.wurlod]

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!

[ArndW]

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.