CODEWITHSUNDEEP
mysqlsql
Oladapo
Oladapo

Reputation: 166

SQL query to check if a data exist in a particular arrays of data

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?

Upvotes: 0

Views: 115

Answers (3)

Igino Boffa
Igino Boffa

Reputation: 451

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

Upvotes: 1

Ralph Ritoch
Ralph Ritoch

Reputation: 3440

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)

Upvotes: 4

kimy82
kimy82

Reputation: 4475

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

Upvotes: 0

Related Questions