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
– jeroen, Commented Feb 7, 2018 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
– EZ SERVICES, Commented Feb 7, 2018 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
– Shaheer Ahmed, Commented Feb 7, 2018 at 10:22

John Woo · Accepted Answer · 2018-02-07 10:43:04Z

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 7, 2018 at 10:43

answered Feb 7, 2018 at 10:23

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Woo Over a year ago

you can also use FIND_IN_SET

Salman Arshad · Accepted Answer · 2018-02-07 10:41:33Z

1

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)";

answered Feb 7, 2018 at 10:41

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Comments

Daniel E. · Accepted Answer · 2018-02-07 10:23:56Z

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 7, 2018 at 10:23

Daniel E.

2,4811 gold badge16 silver badges25 bronze badges

Comments

Sofyan Thayf · Accepted Answer · 2018-02-07 10:22:41Z

-1

Try this:

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

I assumed 2280 always in the same position

answered Feb 7, 2018 at 10:22

Sofyan Thayf

1,3282 gold badges15 silver badges26 bronze badges

Collectives™ on Stack Overflow

Check if row contains a substring from a string

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related