MySQL throwing error "Error Code: 3685. Illegal argument to a regular expression" when cal-CodePudding

As I mentioned in the Q-title, as now the MySQL has been heavily upgraded to version 8, which expectedly provides much new features including shortened characters for Word Boundary matching, calculating exact occurrence of phrase(s) inside column where one row-column(cell) may contain multiple occurrences of searched term/phrase must become easier to achieve.

Yet when I am using this proper looking query to count the exact occurrence of string it throws the below given error:

SELECT 
    ROUND((LENGTH(`column_name`) - LENGTH(REGEXP_REPLACE(`column_name`,
                        "^[[:<:]]Home Depot[[:>:]]$",
                        ''))) / LENGTH('Home Depot')) AS `found`
FROM
    <DB>.<TableName>;

Where if there are 2 rows as below:

Home Depot is a good one but Home Depot
Home Depot is a bad one

Then it must return found(count of total occurrence) as 3 instead of just no. of rows 2. But on the contrary it throws error: Error Code: 3685. Illegal argument to a regular expression.

And if I use \\b instead of the [[:<:]], then it gives too many rows of the order of 1000000 which is ofcourse the wrong count as there aren't that much rows in the entire table, so it's just the Regex engine messing up.

Anyone care to help out achieve what I want ?

CodePudding user response：

You need to change the word boundaries to \\b. And to match Home Depot anywhere in the string, you must remove the ^ and $ anchors. Finally, to get the total number of replacements, you have to use SUM() to add up the values from each row.

I don't think you need to use ROUND() since the numerator should always be an exact multiple of the denominator.

Putting it all together:

SELECT SUM((LENGTH(`column_name`) - 
            LENGTH(REGEXP_REPLACE(`column_name`, "\\bHome Depot\\b", ''))) 
           / LENGTH('Home Depot')) AS found
FROM db.tablename