Count number of fields in ~~ delimited string

[JeroenDmt]

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?

[chulett]

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

[JeroenDmt]

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?

[chulett]

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...

[DSguru2B]

In the filter command put in the following awk command.

Code: Select all

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

This should take care of it.

[JeroenDmt]

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.

[chulett]

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

[DSguru2B]

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.