I am using PHP
I have an SQL Table that looks like so:
I am trying to retrieve all rows which contain "9" in the "FAMILY" Column How would I do so? Note: The numbers are separated by hyphens "-"
Asked
Active
Viewed 50 times
0
-
It won't work if `9` is the last one in `family` – 1000111 Jun 28 '16 at 04:56
-
@1000111 use aggreed not only for that also if it only 9 this will not work – Mahesh Madushanka Jun 28 '16 at 04:58
-
So what would I need to to do in that case? – Ideen Jun 28 '16 at 04:59
-
Select * from tablename; Now use foreach for the records and do it using php code. You can use array functions like strpos() to find element that contains 9. – Aamir Jun 28 '16 at 05:04
-
Ideally you would normalize your table and put one number per row in combination with the ID in another table. – jeroen Jun 28 '16 at 05:05
2 Answers
2
You need to adopt REGEX for that I guess.
SELECT * FROM table_name WHERE family REGEXP '[[:<:]]9[[:>:]]';
Note: May be it's high time you normalized your data. Otherwise you might find it troublesome even for doing the simplest manipulation on that column.
Have a look at this:
Is storing a delimited list in a database column really that bad?
0
Please try this query:
select * from table_name where concat('-',FAMILY,'-') like '%-9-%';
Dipanwita Kundu
- 1,637
- 1
- 9
- 14
-
-
-
Now one more question @dipanwitaKundu If I wanted to ask for both numbers how would I execute that? Say: all "9" and "11" – Ideen Jun 28 '16 at 05:09
-
1You can use regex like : '(?=.*4)(?=.*11)' If you want to search for both 9 and 11 – Hardik Kanjariya ツ Jun 28 '16 at 05:50