none
How to count # of occurrences of a character inside a string? RRS feed

  • Question

  • I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string.  For example, my string in the data flow is:

     

    NNNNNRJKSURNNNEJNNNN

     

    Now I need to count the number of "N"s in that column.  From the example above, I should get the integer 12, and that would be the value of my derived column.  Any ideas?

    Thursday, January 24, 2008 2:52 PM

Answers

  • Or you could implement this in a Derived Column, if the logic is as simple as you are showing. Just get the LEN of the whole string, replace the character you are looking for with an empty string, get the length of the result, and subtract that from the original length. The result is the number of occurrences for the character.

     

    Code Snippet

    LEN(ColumnToCheck) - LEN(REPLACE(ColumnToCheck,"N",""))

     

     

    If it is more complex, you probably will need a script transform, as Phil suggested.

    Friday, January 25, 2008 2:00 AM
    Moderator

All replies

  •  

    You'll have to write this logic in a synchronous script component.  Read in the string as an array (or list, whatever it's called in .Net) and then loop through it counting the number of matches along the way.
    Thursday, January 24, 2008 3:07 PM
    Moderator
  • Or you could implement this in a Derived Column, if the logic is as simple as you are showing. Just get the LEN of the whole string, replace the character you are looking for with an empty string, get the length of the result, and subtract that from the original length. The result is the number of occurrences for the character.

     

    Code Snippet

    LEN(ColumnToCheck) - LEN(REPLACE(ColumnToCheck,"N",""))

     

     

    If it is more complex, you probably will need a script transform, as Phil suggested.

    Friday, January 25, 2008 2:00 AM
    Moderator
  • Hahahaha!  I knew there was an easier way, I was just thinking too hard!
    Friday, January 25, 2008 2:08 AM
    Moderator
  • What a great snippet of code!...

    MCSA: (SQL 2012, SQL 2008, Windows Server 2008) MCITP: (SQL BI & SQL 2008) MCT: 2000 - 2013 SQL Training London

    Wednesday, May 8, 2013 5:11 PM
  • This snippet only have problem with spaces.

    Code Snippet

    LEN(ColumnToCheck) - LEN(REPLACE(ColumnToCheck,"N",""))

    Miserably, if you have double ore more spaces their number will be added to final result.

    Saturday, September 7, 2013 4:32 PM
  • You made it so simple bro! Very good. Cheers mate!

    Raja

    Tuesday, May 5, 2015 2:49 AM
  • HI! I did not understand the explanation of your answer. Can you make it more understandable? 
    Thursday, August 3, 2017 5:22 PM
  • Sure:

    GOT APPLES

    How many P's are in that string?

    len("GOT APPLES") - LEN(REPLACE("GOT APPLES","P","")) = 

    10 - LEN("GOT ALES") = 

    10 - 8 = 2

    A very good answer.... That's one of those types of answers we would use in those old Apple Basic 1-line programs where you'd write a program in 255 characters.  Used to love those things.

    And to be honest, I used this snippet of code to see how many line feeds were in my string by replacing CHAR(10) and it gave me a line count  :)  

    Anyway, I know it's 1.5 years old but I had to give kudos.

    Tuesday, February 12, 2019 11:28 PM
  • I want to count the number of digits after a decimal that goes from no decimals to 6 places 0-0.123456 in one result to then truncate a different result to that amount of decimal places?
    Thursday, February 28, 2019 2:46 AM