CODEWITHSUNDEEP
phpmysqlsqlstring
EZ SERVICES
EZ SERVICES

Reputation: 113

Check if row contains a substring from a string

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 
----+------------------

Upvotes: 2

Views: 1118

Answers (4)

John Woo
John Woo

Reputation: 263683

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.

Upvotes: 4

Salman Arshad
Salman Arshad

Reputation: 272006

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

Upvotes: 1

Daniel E.
Daniel E.

Reputation: 2480

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 ;

Upvotes: 0

Sofyan Thayf
Sofyan Thayf

Reputation: 1328

Try this:

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

I assumed 2280 always in the same position

Upvotes: -1

Related Questions