Mysql: find string with most matching starting characters

Question

I have a problem I would like to know if I can tackle in my mysql query.

I have a list of "names" in my DB: id=1, name="name1" id=2, name="name11" id=3, name="name111"

I would like to get the id of the name in db with the most common characters at the beginning.

=> name111someignorechar would return 3

=> name11someignorechar would return 2

=> name1someignorechar would return 1

Any idea?

If I use the LIKE keyword, SELECT id FROM names WHERE CONCAT(name,"%") LIKE "name111someignorechar" It will return me 3 results !

Are you looking to return 3, 2 and 1 or are you trying to return the content of the name="<something>" i.e. <something> or a different value? — kaj
– kaj, Commented Mar 3, 2012 at 12:29

Vedmant · Accepted Answer · 2013-01-18 23:24:15Z

2

Try this:

select name
from names
where 'name111someignorechar' like concat(name,'%')
order by length(name) desc
limit 1

answered Jan 18, 2013 at 23:24

Vedmant

2,6701 gold badge31 silver badges36 bronze badges

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

Comments

TinkerTank · Accepted Answer · 2013-08-20 07:32:32Z

The current answer does work for the example that the OP has given, but it doesn't really answer the question. The OP asks for a query that orders by the most common characters, but the query only works if the DB entry contains a subset of the characters in the search query, and nothing more.

For example, comparing name111someignorechar with name1otherignorechar wouldn't work.

There's another solution, as explained here: http://tech-q-a.tumblr.com/post/58713827644/how-to-sort-results-based-on-the-number-of-identical

We can use a MySQL function that counts the number identical starting-characters for two strings:

DROP FUNCTION IF EXISTS countMatchingBeginChars;
delimiter // 
CREATE FUNCTION `countMatchingBeginChars`( s1 text, s2 text ) RETURNS int(11)
    DETERMINISTIC
BEGIN 
    DECLARE s1_len, s2_len, min_len, j INT; 
    SET s1_len = LENGTH(s1), s2_len = LENGTH(s2);

    IF s1_len > s2_len THEN  
      SET min_len = s2_len;  
    ELSE  
      SET min_len = s1_len;  
    END IF; 
    SET j = 0;
     WHILE j <= min_len DO 
        SET j = j + 1;
        IF SUBSTRING(s1,j,1) != SUBSTRING(s2,j,1) THEN
            RETURN j-1;
        END IF;
     END WHILE;
     RETURN min_len;
END//
delimiter ;

So,

SELECT countMatchingBeginChars("name111someignorechar", "name11")

would return 6 (as above), but so would

SELECT countMatchingBeginChars("name111someignorechar", "name11otherignore")

Now, we can sort the entries in the database according to the number of matching characters:

SELECT name FROM names ORDER BY countMatchingBeginChars("name111someignorechar",name) DESC, name

If only the entry with the most matching characters is required, we can just return only one result.

SELECT name FROM names ORDER BY countMatchingBeginChars("name111someignorechar",name) DESC, name LIMIT 1

Collectives™ on Stack Overflow

Mysql: find string with most matching starting characters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related