count characters matching in CONCAT MySQL REGEXP

Question

I have the following MySQL query which works

SELECT *,
CONCAT( office, ' ', contactperson ) AS bigDataField
FROM webcms_mod_references 
HAVING bigDataField REGEXP "one|two"

Now there is no ORDER BY and if: - bigDataField contains "one" this field is shown - bigDataField contains "one two" this field is shown aswell

now it depends on the id which one of those is shown first, but I want the one with the more matches to be shown first!

I tried with

SUM(
    CASE WHEN bigDataField REGEXP "one|two"
         THEN 1 
         ELSE 0 END
    ) AS matches

But that does not work. Can anyone help me. I think the best would be as the title says to count the matching charachters from the REGEXP. If there are other ways please explain.

The REGEXP is a user input, so, I'm trying to implement a small search over a small Database.

Moob · Accepted Answer · 2013-11-22 17:03:18Z

4

This is theoretical whilst sqlfiddle is down but you might have to split the REGEXP into two so you can count the matches. REGEXP will return either a 0 or 1. Either it matched or didn't. There's no support for finding how many times it was matched in a string.

SELECT *,
CONCAT( office, ' ', contactperson ) AS bigDataField
FROM webcms_mod_references 
HAVING bigDataField REGEXP "one|two"
ORDER BY (bigDataField REGEXP "one" + bigDataField REGEXP "two") DESC

answered Nov 22, 2013 at 17:03

Moob

16.5k1 gold badge40 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Noam Rathaus Over a year ago

SQLFiddle down, stackoverflow answer rate on mysql goes to the drain :)

Mosty Mostacho Over a year ago

This is a good solution. I was about to post it after I wrote mine :) Only thing is that you should inversely order the field (use a DESC)

danigore Over a year ago

Super! I find this for more than 2 hours! Thank you

Mosty Mostacho · Accepted Answer · 2013-11-22 17:00:32Z

2

There is no way to count the amount of matches on a regex. What you can do is match them separately and order by each of those matches. EG:

SELECT *,
CONCAT( office, ' ', contactperson ) AS bigDataField
FROM webcms_mod_references 
HAVING bigDataField REGEXP "one|two"
ORDER BY
    CASE WHEN bigDataField REGEXP "one" AND bigDataField REGEXP "two" THEN 0
         ELSE 1 -- The else should catch the "two" alone or the "one" alone because of the filtering
    END

Of course, you can use a LIKE here too but maybe your regex are more complex than that :)

answered Nov 22, 2013 at 17:00

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

1 Comment

caramba Over a year ago

I'll try this, thanks. Yes they are a bit more complex. Maybe I can share a live example in a few hours

Noam Rathaus · Accepted Answer · 2013-11-22 17:04:22Z

0

When I want to count some substring I do replace and "-" the length, example:

SELECT (
        LENGTH('longstringlongtextlongfile') - 
        LENGTH(REPLACE('longstringlongtextlongfile', 'long', ''))
       ) / LENGTH('long') AS `occurrences`

I think this is an elegant solution for a problem of counting how many times 'long' appears inside provided 'string'

answered Nov 22, 2013 at 17:04

Noam Rathaus

5,6082 gold badges31 silver badges37 bronze badges

1 Comment

Moob Over a year ago

Clever. Couple that with a REGEXP_REPLACE UDF and you could indeed get a count of matches.

danigore · Accepted Answer · 2020-01-28 19:59:08Z

This is not especially the answer to this question, but I think strongly attached to it... (And I hope, will help someone, who cames from google, etc)

So if you use PHP (if not, may dont keep reading ...), you can build the query with that, and in this case, you can do this (about @Moob great answer):

function buildSearchOrderBy(string $regex, string $columName, string $alternateOrderByColumName): string
{
    $keywords = explode ('|', $regex);
    if (empty ($keywords)) {
        return $alternateOrderByColumName;
    }

    $orderBy = '(';
    $i = 0;
    foreach ($keywords as $keyword) {
        $i++;
        if ($i > 1) $orderBy .= " + ";
        $orderBy .= "IF((" . $columName . " REGEXP '" . $keyword . "')>0, " . (100 + strlen($keyword)) . ", 0)";
    }
    $orderBy .= ')';

    return $orderBy;
}

So in this case every match worth 100 + so many scores, what the numbers of the characters in the current keyword. Every match starting from 100, because this ensure the base, that the first results will be these, where the total score originate from the more matches, but in proportionally worth more a longer keyword in any case.

Builded to one column check, but I think you can update easy. If copied to your project, just use like this (just an example):

    $orderBy = buildSearchOrderBy($regex, 'article.title', 'article.created');

    $statement = "SELECT *
        FROM article
        WHERE article.title REGEXP '(" . $regex . ")'
        ORDER BY " . $orderBy . " DESC"
    ;

Collectives™ on Stack Overflow

count characters matching in CONCAT MySQL REGEXP

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related