Count number of fields in ~~ delimited string
Moderators: chulett, rschirm, roy
Count number of fields in ~~ delimited string
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?
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?
In the filter command put in the following awk command.
This should take care of it.
Code: Select all
awk -F"\~~" '{print NF}'
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Thanks a lot. That does take care of it indeed.DSguru2B wrote:In the filter command put in the following awk command.This should take care of it.Code: Select all
awk -F"\~~" '{print NF}'
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.
NOTE: pxEreplace() is not an IBM product. Its user written and can be found in FAQ Discussion forum.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.