0

I created a table for my cellphone contacts and I want to sort the cellphone numbers. Example if the phone number starts with 1 to 3 it belongs to the first telecommunications company and 4 to 6 belongs to other.

What I want to do is if the user enters their phone number the number will be inserted to the table of the telecom company but each telecom company is dependent on the ContactId which is the primary key in cellphoneContacts table.

I already tried using cellnum1 but nothing works

cellnum1 ENUM('1%','2%','3%') ............. 
cellnum1 VARCHAR(11) CHECK (cellnum1 IN ('1%','2%','3%')) ......... 
cellnum1 VARCHAR(11) CHECK (cellnum1 LIKE('1%','2%','3%'))
Piyush Gupta
  • 2,181
  • 3
  • 13
  • 28
ZeroCool
  • 437
  • 1
  • 7
  • 23

2 Answers2

0
SELECT * FROM tbl_item WHERE SUBSTR([<Column name>], 1, X) = '<Your ID prefix>' ORDER BY [<Column name>]

Where X from SUBSTR is howmany characters do you want. And please, be more specific with what you want in the future. :)

Andreea Dumitru
  • 96
  • 1
  • 11
  • There's the cellphoneContacts table , teleCom1, teleCom2, and teleCom3 tables. The 3 teleCom tables has a foreign key ContactId from the cellphoneContacts table. I want to store the cell numbers entered from cellphonecontacts into these 3 tables. Example if the numbers starts 1 - 3 it will be automatically stored in teleCom1 with a corresponding value of the foreign key to know whom that number was. – ZeroCool Feb 16 '16 at 11:02
  • You have to create a trigger to do this automatically :) – Andreea Dumitru Feb 16 '16 at 12:44
  • Could you please provide a link for that and thank you for your answer. – ZeroCool Feb 17 '16 at 01:13
0

The MySQL Reference Manual says:

The CHECK clause is parsed but ignored by all storage engines.

Try a trigger...

If you are checking first character value , you can use substr on the NEW table rows to filter data :

substr(NEW.cellnum1 ,1,1) in ( 1,2,3)
Kobi
  • 2,494
  • 15
  • 30
  • What if I want to check the first 4 number? – ZeroCool Feb 16 '16 at 10:55
  • You have to change the parameters of the `Substring` function: `substr(cellnum1 ,1,4)`, look at the MySQL documentation and change the list of value in the `IN` (http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substr) – Kobi Feb 16 '16 at 10:59
  • Thank you this is a great help. – ZeroCool Feb 17 '16 at 01:13