SQL Query on considering a like statement

Question

I am trying to add a column, YES/No, based on text involved. If the Value column contains BALL, we mark it as Yes in the column. But if the 'BALL' is attached with any text/string, it should return it as 'NO'. There are some cases which I have attached

How do I form the case statement so that if any text/string is attached to the 'BALL' without a space should be No, and rest all the cases should be 'Yes'. I tried using like %BALL%, but it does not satisfy all the examples in the above screenshot.

Answer 1

Set @val variable with value need to lookup:

SET @val := 'BALL';

Run query with two type of checking:

1. Use LOCATE to find @val value in the table; will return numerical position of the first character of the searched value.
2. Use SUBSTRING to get two sets of value from string_val column; using the numerical position that was obtained using LOCATE:
   • val1 will return the value of string_val before the searched value of @val (the LOCATE result need to have a deduction of 1).
   • val2 will return the value of string_val that matches @val and anything after it.
3. In the outer query, chk1 checks the last character extracted to val1 against alphabets using REGEXP. In this case GOBALL will return true (1) while 9232BALL and 9232BALLV will return false (0). Here we'll look at whatever false.
4. chk2 checks if val2 matches the searched @val. Therefore, the separated value of 9232BALL which end up getting BALL for val2 will return true (1) while 9232BALLV which end up getting BALLV for val2 will return false (0). Here we'll look at whatever is true.
5. The last filter is checking the addition of chk1+chk2. The result we're looking for is 1 because chk1 need to be false (0) and chk2 need to be true (1).

SELECT String_val AS 'Value',
       CASE WHEN chk1+chk2=1 THEN 'Yes' ELSE 'No' END AS 'Yes/No'
FROM
(SELECT *,
       RIGHT(val1,1) REGEXP '[a-zA-Z]' AS chk1,
       val2=@val AS chk2
  FROM
(SELECT string_val,
        SUBSTRING(string_val,1,LOCATE(@val,string_val)-1) val1,
        SUBSTRING(string_val,LOCATE(@val,string_val)) val2
FROM mytable) A) B

Alternative option 1:

SELECT string_val, 
       CASE WHEN 
       REGEXP_REPLACE(CASE WHEN val1 REGEXP '[a-zA-Z]$' = 1 
                      THEN CONCAT(val1,val2) ELSE val2 END,'[0-9]','')=@Val 
       THEN 'Yes' ELSE 'No' END AS 'Yes/No'
  FROM
(SELECT string_val,
        SUBSTRING(string_val,1,LOCATE(@val,string_val)-1) val1,
        SUBSTRING(string_val,LOCATE(@val,string_val)) val2
FROM mytable) A;

Alternative option 2:

My further testing shows that it's possible to get the result using REGEXP_SUBSTR with a much shorter query:

SET @val := 'BALL';

SELECT string_val, 
       REGEXP_SUBSTR(string_val, '[a-zA-Z]+[BALL]+[a-zA-Z]') AS vals,
      IF(((SELECT vals))=@val,'YES','NO') AS 'Yes/No'
FROM mytable;

Demo

Answer 2

Just insist that the previous and next characters are not letters:

(case when value regexp '([^a-zA-Z]|^)BALL([a-zA-Z]|$)'
      then 'YES' else 'NO'
 end)

Here is a db<>fiddle.