CODEWITHSUNDEEP
mysqllaravelstring
Marty
Marty

Reputation: 312

MySQL - How to find through query if a string contains substring in Column

I am a beginner with MySQL and trying to write a query to find if a string contains a substring in Column through if statement,

Let's say for example I have a column in a table with String values

This is an example of String

Through the query will try to check if "This is an example of String" contains This is in then it may return either yes/1 or no/0 for not containing this is

But I couldn't have an idea how should I write with if statement in MySQL query

Could anyone help me with this? please let me know if this is the correct way and possible through MySQL query?

Upvotes: 1

Views: 5063

Answers (1)

Akina
Akina

Reputation: 42728

Your query is correct except the comma instead of IN keyword must be used.

SELECT POSITION('this is', column1) AS result FROM table;

Also you may use not POSITION but LOCATE function (they are aliases as mentioned in comments)

SELECT LOCATE('this is', column1) AS result FROM table;

or INSTR() function (in this case you must sswap operands)

SELECT INSTR(column1, 'this is') AS result FROM table;

All queries returns positive integer if the substring is found, zero 0 if the substring is not found, and NULL if any operand is NULL.

If you want to return 1/0 output only then you must check what one of these variants is obtained, and convert to needed value. For example,

SELECT CASE WHEN LOCATE('this is', column1)
            THEN 1
            ELSE 0
            END AS result FROM table;

LOCATE('this is', column1) is treated as TRUE returning 1 when the substring is found, and FALSE returning 0 otherwise.

Pay attention - if you use case-sensitive collation then 'this is' substring will not be found in 'This is an example of String' value!

If you need to apply case-insensitive search then you may specify case-insensitive collation explicitly, or apply a function which converts all symbols to lower or upper case (of course, literals does not need in this, they can be printed in needed case). For example,

SELECT LOCATE('this is', LOWER(column1)) AS result FROM table;

Upvotes: 1

Related Questions