CODEWITHSUNDEEP
phpmysqlregex
frosty
frosty

Reputation: 2649

Match by char length in a SELECT statement

I have a SELECT statement. I'm wondering if it's possible to match by char length in a SELECT statement? So, for example. If a word has a character length of 3 or less, and if it matches that word, it would count as 1 match. If a word has a character length of over 3, and if it matches that word, it will count as 2 matches. Is that possible?

SELECT colName, 
( colName LIKE  '% 22 %' ) + 
( colName LIKE  '% 333 %' ) + 
( colName LIKE  '% 4444 %' ) as matches
FROM tableName
HAVING matches > 0 
AND matches = (select max(
( colName LIKE  '% 22 %' ) + 
( colName LIKE  '% 333 %' ) + 
( colName LIKE  '% 4444 %' )
) from tableName) limit 30

EXAMPLE:

22 = 1 match
333 = 1 match
4444 = 2 matches

Upvotes: 0

Views: 40

Answers (1)

Barmar
Barmar

Reputation: 780899

use IF or CASE

SELECT colName,
    (colName LIKE '% 22 %') +
    (colName LIKE '% 333 %') +
    IF(colName LIKE '% 4444 %', 2, 0) AS matches

You could also use multiplication:

SELECT colName,
    (colName LIKE '% 22 %') +
    (colName LIKE '% 333 %') +
    2 * (colName LIKE '% 4444 %') AS matches

Upvotes: 1

Related Questions