Count number of character occurrence in string

Jmoreno
Jmoreno Member
in Magic ETL

Trying to see if there is a way to achieve this.

I have a column that has something like the left column here

image.png

Is there a way to achieve the right side column by looking at maybe the number of commas + 1 maybe?

Doing this in ETL formula tile would be best for me if possible

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can calculate the length of the string, remove the character you want, recalculate the length and take the difference.

    LENGTH(`Color`) - LENGTH(REPLACE(`Color`, ',', '')) + 1
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can calculate the length of the string, remove the character you want, recalculate the length and take the difference.

    LENGTH(`Color`) - LENGTH(REPLACE(`Color`, ',', '')) + 1
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories