Scenario:Count the no of vowels present in emp_name column of EMP table as shown bellow.
     emp_name         total_vowels_count
       Allen                          2
       Scott                          1
       Ward                         1

These are the steps to achieve it

  1. Connect required columns from SQF to an expression transformation.
  2.  In Expression add 6 columns like in the picture as bellow. But You can make it two columns( One for all the vowels and one for the vowel counts). For better understanding I have added 6 columns,5 for each of the vowels and one for the vowel count.

    The way I achieved is for each of the vowels in ename , I replaced it with null and in port total vowel count , I  substract the vowel port from the ename length which gives me the individual count of vowels, after adding up for all vowels I found all the vowels present. Here are  all the  variable ports.
    For A  write                              REPLACECHR(0,ENAME,'a',NULL)
    For E  write                             REPLACECHR(0,ENAME,'e',NULL)
    For I  write                              REPLACECHR(0,ENAME,'i',NULL)
    For O  write                            REPLACECHR(0,ENAME,'o',NULL)
    For U  write                           REPLACECHR(0,ENAME,'u',NULL)
    And for o/p column total_vowels_count write expression  like this
    (length(ENAME)-length(A))
    +
    (length(ENAME)-length(E))
    +
    (length(ENAME)-length(I))
    +
    (length(ENAME)-length(O))
    +
    (length(ENAME)-length(U))
  3. Finally send to target.

This could have been done a different or a better way, If you have suggestion don't forget to comment.