Use the first match count of matches in select statement

Question

I have a SELECT statement that returns rows from the database depending on the matches that it finds. However, I would like to change this SELECT statement a bit. Instead of it getting all the matches, and adding them up to get the final count of all the matches, I would like it to stop adding the matches once it gets ONE match. I have an example, below the code, which will explain in more detail:

$text1 = "IF ( input LIKE  '% i am here %', 3, 0 )";
$text2 = "IF ( input LIKE  '% i am %', 2, 0 )";
$text3 = "IF ( input LIKE  '% i %', 1, 0 )";

SELECT colName, $text1+$text2+$text3 as 'matches' 
FROM tableName

Example

There's a row with text " i am here ";

With the current SELECT statement, it'll get 3+2+1=6 matches. But instead of 6 matches, I just want the match of the first match, which is 3. So, if the SELECT statement finds a match, I want it to stop, and just use the match count of that match, and not use the match count of the rest of the matches. How do I do that, if at all possible?

Answer 1

Try to use CASE WHEN operator.

Try this:

$text1 = "CASE WHEN input LIKE  '%i am here %' THEN 3 
          ELSE
              CASE WHEN  input LIKE  '% i am %' THEN 2
              ELSE 1  
              END
          END ";
SELECT colName, $text1 as 'matches' 
FROM tableName

SQL Fiddle