czmudzin czmudzin - 6 months ago 24
SQL Question

Count Substring Occurences in MYSQL using Case Statement

I have a string of text that contains multiple substrings within that I'd like to count the occurences of. I saw solutions to a similar question, but I'm wondering if this can be done using a case statement or without creating a new table.

Basically, I'm using a data aggregation/analysis tool that only accepts very basic MySQL statements.

Sample:

Paid Search:CLICK, Display:RICH_MEDIA, Display:RICH_MEDIA, Display:RICH_MEDIA, Display:RICH_MEDIA, Display:CLICK, Display:IMPRESSION, Display:IMPRESSION, Display:RICH_MEDIA, Display:RICH_MEDIA, Display:IMPRESSION, Display:CLICK


I'd like to extract a count of "Display:Rich_Media"

Answer

Here you can accomplish this by the following query:

SET @your_string :=
'Paid Search:CLICK, Display:RICH_MEDIA, Display:RICH_MEDIA, Display:RICH_MEDIA, Display:RICH_MEDIA, Display:CLICK, Display:IMPRESSION, Display:IMPRESSION, Display:RICH_MEDIA, Display:RICH_MEDIA, Display:IMPRESSION, Display:CLICK';

SELECT
    ROUND(
        (
            LENGTH(@your_string) - LENGTH(
                REPLACE (
                    @your_string,
                    "Display:RICH_MEDIA",
                    ""
                )
            )
        ) / LENGTH("Display:RICH_MEDIA")
    ) AS totalOccurrence;

Explanation:

  • Length of your string - length after the all the occurerences of the subtring replaced by empty
  • The result of this subtraction = number of characters you replaced = length of your subtring * number of occurrences of the subtring
  • Now if you divide the result by the length of the subtring you will get the number of occurrences.
Comments