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

Question

I am 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

I gave it try with the following query:

SELECT POSITION("this is" IN column1) AS result
FROM table;

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

POSITION() is essentially a pointer to LOCATE(), so you can see the details about using LOCATE() here (dev.mysql.com/doc/refman/8.0/en/…), ie. its return values if the string is not found, or a parameter is Null. Either CASE or IF() would solve your need to return either a 1 or 0, depending on whether the string is found or not (dev.mysql.com/doc/refman/8.0/en/flow-control-functions.html) — Craig
– Craig, Commented Apr 28, 2021 at 6:15
@Strawberry - seems you didn't understand the question :) I don't want to replace the substring, want to find either of boolean value through if statement — Marty
– Marty, Commented Apr 28, 2021 at 6:28
@Craig - Thank you for the replay, Isn't the POSITION() and LOCATE() MySQL function works same ? — Marty
– Marty, Commented Apr 28, 2021 at 6:30
@user212010909097 If you use LOCATE instead of POSITION then the only difference is that the query become 2 symbols shorter. No another difference. — Akina
– Akina, Commented Apr 28, 2021 at 6:39

Akina · Accepted Answer · 2021-04-28 06:33:10Z

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;

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related