0

My table structure looks like this

id, Name, Phone

<table>
  <thead>
    <tr>
      <th>Id</th>
      <th>Name</th>
      <th>Phone numbers</th>
      
    </tr>
  </thead>
  <tbody>
      <tr>
        <td>1</td> 
        <td>Peter</td> 
        <td>1736736,67358467,553463563</td> 
      </tr>
  </tbody>
</table>

So Peter has 3 numbers separated by commas in the phone column

So when a particular number calls I would like to check if it exist in the column phone. Something like

if 67358467 exist in column phone. The issue is the values in this column are separated by commas.

So what SQL query am I to use?

Oladapo
  • 157
  • 1
  • 2
  • 9
  • this is why it's almost always such a bad idea http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 – e4c5 Dec 01 '16 at 09:41
  • Are you trying to find stuff in an HTML table or a sql table??? – e4c5 Dec 01 '16 at 09:44
  • Your example is HTML. Why do you want an SQL query? – Seb Dec 01 '16 at 09:48
  • No it is a MySQL table I just showed it structure using the HTML code – Oladapo Dec 01 '16 at 09:53

3 Answers3

4

There is a string function in MySQL called FIND_IN_SET which will help you find a number in a set.

Example...

SELECT * FROM tablename WHERE FIND_IN_SET(1736736,numbers)
Ralph Ritoch
  • 3,260
  • 27
  • 37
1

The problem is how the table is defined. A single attribute should never contain multiple data. Think about using a table to join ID and numbers. So, you'll have a table mapping ID and person (1 to 1) and another table mapping ID and numbers (1 to n)

PEOPLE:
ID       NAME
1        Peter

PHONE_NUMBERS
ID      NUMBER
1       1736736
1       67358467
1       553463563

Your query will be:

select 1 from PEOPLE,PHONE_NUMBERS where NUMBER=<wanted_number> and PEOPLE.ID=PHONE_NUMBERS.ID

If result of this query is 1, then the number exists

Igino Boffa
  • 411
  • 2
  • 6
  • 26
0

Something like: where phone like '%,number,%'

kimy82
  • 4,069
  • 1
  • 22
  • 25
  • So then add: or phone like '%,number' or phone like 'number,%' – kimy82 Dec 01 '16 at 09:49
  • Same problem as the other answer. You might receive false positives if it matches only a part of the number. – Seb Dec 01 '16 at 09:50
  • Help me write the query. I am a little confuse at the moment – Oladapo Dec 01 '16 at 09:52
  • Do not think that. If doing 'number,%' if checks the column starts with the number plus a comma and doing '%,number' it checks the column ends with the number – kimy82 Dec 01 '16 at 09:53
  • select * from table_name where phone like '%,number,%' or phone like '%,number' or phone like 'number,%' – Oladapo Dec 01 '16 at 10:02