DSXchange

I want to count the number of fields in a ~~ delimited string.
For example if the string is "field 1~~field2~~field3' the result should be 3.

I thought I could do this using the Count function (and add 1) or the DCount function.

Though when the string is 'field1~~~~field3', I'd like the result to be 3 as well, but in this case Count(string,'~~')=3 and DCount(string,'~~')=4, because there is 3 ways it can find a ~~ in the string.

Is there a way I can get the right count of the ~~ delimiters in Datastage?

Count single occurances, divide by 2 and then add 1.

chulett wrote:Count single occurances, divide by 2 and then add 1.

Smart.

Is there also a way to count the 'real' ~~ delimiters in case a ~ could occur in a string?
So that when the string is 'field~1~~field~2~~field~3' the result is still 3?

Would that be valid in your world? Not sure off the top of my head how to avoid the problem you've seen when checking for multiple-character delimiters. Pondering...

In the filter command put in the following awk command. This should take care of it.

DSguru2B wrote:In the filter command put in the following awk command.

Code: Select all

awk -F"\~~" '{print NF}'

This should take care of it.

Thanks a lot. That does take care of it indeed.

Still curious how one would do this without leveraging 'outside' components...

Well, it can be done using EREPLACE() and count together. Use EREPELACE() to change '~~' to something that is most likely not going to show up in the data. Something like ! or & and do a count on that. In px, pxEreplace() can be used to replace the double tilda.
NOTE: pxEreplace() is not an IBM product. Its user written and can be found in FAQ Discussion forum.