Check if row contains a substring from a string

Question

I want to check if the table ssd has a row containing the value 2280 (a substring) and it does but the problem is I can only get a string to check which contains multiple values including the substring. The table ssd:

----+-------------------
 id | m2len
----+-------------------
  1 | 2280
----+-------------------
  2 | 2260 
----+------------------

The query:

$m2len = "2242 2260 2280 22110"
$sql = "SELECT m2len FROM ssd WHER m2len LIKE '%$m2len%'";

This query gives no output even if the table ssd has the row m2len containing the value 2280.

Is there a way to check whether a row contains a substring (2280) from a string (2242 2260 2280 22110) with MySQL?

Note: This question may look similar to SELECT MySQL field that contains a substring but it's not as the other question simply checks if a string contains a substring but here I am trying to do the opposite.

Update:

@JohnWoo's Answer Worked. But there's a problem for other values like 0, 80, 22 it returns those rows as well which is not required. I want an exact match.

The table may also contain values like:

id | m2len
----+-------------------
  1 | 2280
----+-------------------
  2 | 2260 
----+------------------
  3 | 0
----+------------------
  4 | 80 
----+------------------

What is the exact problem, do you need to extract only the third value from your string, do you want to match on all values, etc.? — jeroen, Feb 07 '18 at 10:18
The row contains only one value i.e `2280` but the string I have to check has multiple values `2242 2260 2280 22110`. Now I cannot use `LIKE %'2242 2260 2280 22110'%` to look for `2280` — EZ SERVICES, Feb 07 '18 at 10:20
Seems like you want to check against each element in the string. You could break the variable `$m2len` into an array() using _explode_ `$m2len_array = explode(" ",$m2len);` Use a foreach on the `$m2len_array` to build the where part of your query against each array element. — Shaheer Ahmed, Feb 07 '18 at 10:22

John Woo · Answer 1 · 2018-02-07T10:43:04.080

4

You need to interchange the value and the column in your query,

$m2len = "2242 2260 2280 22110"
$sql = "SELECT m2len FROM ssd WHERE '$m2len' LIKE CONCAT('%', m2len, '%')";

Here's a Demo

There is another solution which uses [FIND_IN_SET()] but the values must be separated by a comma.

$m2len = "2242 2260 2280 22110";
$m2len = str_replace(" ",",",$m2len);
$sql = "SELECT m2len FROM ssd WHERE FIND_IN_SET(m2len,'$m2len')";

Here's a Demo.

edited Feb 07 '18 at 10:43

answered Feb 07 '18 at 10:23

John Woo

258,903
69
498
492

you can also use `FIND_IN_SET` – John Woo Feb 07 '18 at 10:51

score 1 · Accepted Answer · answered Feb 07 '18 at 10:41

Transform this:

"2242 2260 2280 22110"

Into this:

"2242, 2260, 2280, 22110"

And build your query. The following is an over simplified version:

$m2len = "2242 2260 2280 22110";
$csv = str_replace(" ", ", ", $m2len);
$sql = "SELECT m2len FROM ssd WHER m2len IN ($csv)";

score 0 · Answer 3 · answered Feb 07 '18 at 10:23

0

As you are in php, you can use it, you should take care of sql injection, but here is not the question :

$m2len = "2242 2260 2280 22110";
$prepareM2len = "(".substring(" ",",",$m2len).")";
$sql = "SELECT m2len FROM ssd WHER m2len in ".$prepareM2len ;

answered Feb 07 '18 at 10:23

Daniel E.

2,440
1
14
24

score -1 · Answer 4 · answered Feb 07 '18 at 10:22

-1

Try this:

$sql = "SELECT m2len FROM ssd WHERE m2len = MID('$m2len',11,4)";

I assumed 2280 always in the same position

answered Feb 07 '18 at 10:22

Sofyan Thayf

1,322
2
14
26

Check if row contains a substring from a string

4 Answers4