MySQL Query String Contains

MySQL query String contains

Quite simple actually:

SELECT *
FROM `table`
WHERE `column` LIKE '%{$needle}%'

The % is a wildcard for any characters set (none, one or many). Do note that this can get slow on very large datasets so if your database grows you'll need to use fulltext indices.

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

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;

MySQL Query to extract only a specific value that not in a string

You seem to be describing set data with either commas or spaces as delimiters. Instead of going into the typical lecture on storage of serialised data, I will just assume there is nothing you can do about it.

Obviously, passing strings directly into your SQL like this poses a SQLi risk and you should be using parameterised queries or at least some robust sanitisation and validation of the user input.

So, treat the set as a set by turning the space separated lists into comma separated lists -

WHERE FIND_IN_SET('$findit', REPLACE(`comment`, ' ', ',')) > 0

FIND_IN_SET

REPLACE

PREPARED STATEMENTS

UPDATE

This db<>fiddle works for the examples you have provided. Please provide a specific example of value(s) for which this does not work. Using REGEXP as suggested by Kendle/Bill Karwin may be the better route to go down.

MySQL query select where columns contains numbers and letters

You could use a regular expression:

SELECT licenseplate, DATE_FORMAT(date, '%d/%m/%y'), mileage FROM auto_new WHERE licenseplate REGEXP '(?=.*\d)(?=.*[A-Z])[\d|\w]*' AND Length(Address) = 6 GROUP BY licenseplate, mileage

This will make sure only license plates that have a length of 6 and contain only alphanumeric characters are returned.