Checking the presence of a number in a string on a single row in MySQL

Question

So I have this string in my MySQL Database:
The string is on a single row: 25 12 6 3 780

-I want to check if for example a variable $data='6', is in the string above;
Return the value of the data if TRUE, and FALSE otherwise;

SELECT * FROM table_name WHERE column_name LIKE '%$data%'

doesn't work because it returns all the string...
Any ideas?

It returns all the string, isn't that what you want? or all strings? — sagi
– sagi, Commented Feb 18, 2016 at 13:13
@sagi I want to check if the value of $data is in the string, and return it if true; — 21scripting
– 21scripting, Commented Feb 18, 2016 at 13:45

21scripting · Accepted Answer · 2016-02-18 15:05:25Z

2

This will returns how many times the exact data exists in particular stings:

Solution 1:

$query = "
    SELECT COUNT(*) AS dataCount 
    FROM table_name t 
    WHERE FIND_IN_SET('".$data."',REPLACE(t.column_name,' ',',')) > 0";

php version(1):

$result=mysql_query("SELECT COUNT(*) AS dataCount FROM table_name t WHERE FIND_IN_SET('".$data."',REPLACE(t.column_name,' ',',')) > 0");
$data=mysql_fetch_assoc($result);
$value=$data['dataCount'];

This will returns all the strings where the exact data exists in:

Solution 2:

$query = "
    SELECT * 
    FROM table_name t 
    WHERE FIND_IN_SET('".$data."',REPLACE(t.column_name,' ',',')) > 0";

php version(2):

  $result=mysql_query("SELECT * FROM table_name t WHERE FIND_IN_SET('".$data."',REPLACE(t.column_name,' ',',')) > 0");
  $data=mysql_fetch_row($result);

edited Feb 18, 2016 at 15:05

21scripting

578 bronze badges

answered Feb 18, 2016 at 13:23

mitkosoft

5,3361 gold badge15 silver badges32 bronze badges

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

2 Comments

21scripting Over a year ago

@mitiksoft , the first solution works just fine for my problem, it returns what is should, now I will see how to return the value in php to assign it to a variable, thanks for the help!

mitkosoft Over a year ago

It will be great if you are stop using mysql_* functions, use mysqli_* instead.

ScaisEdge · Accepted Answer · 2016-02-18 13:22:42Z

0

You can use if (exists (...))

 select if (exists (SELECT * 
           FROM table_name 
           WHERE column_name LIKE '%$data%'), 1,0) ;

answered Feb 18, 2016 at 13:22

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Checking the presence of a number in a string on a single row in MySQL

2 Answers 2

Solution 1:

php version(1):

Solution 2:

php version(2):

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Solution 1:

php version(1):

Solution 2:

php version(2):

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related