0

I have a table in mysql which contains customers names and phone numbers and I want to select customers whose phone number contains only digits because some customer's phone number is wrong and contains special characters and letters.

xanadev
  • 751
  • 9
  • 26
Ahmed Badreldin Amado
  • 41
  • 9
  • You can check the special character using regex. Please have a look at link https://stackoverflow.com/questions/14604736/sql-match-special-characters-regexp – Fahad Anjum May 08 '18 at 10:51
  • you should first know what phone number format you are trying to validate. then you can use a custom REGEX to accomplish that. – xanadev May 08 '18 at 10:54

2 Answers2

1

Let say your table is called customer, with column phone, this query below returns rows where phone is numbers only. 

SELECT * FROM customer WHERE phone REGEXP '^[0-9]+$';
Shuwn Yuan Tee
  • 5,578
  • 6
  • 28
  • 42
0

1) You can make the database fiels as 'int' data type and make validation for user input first. Then users will not enter the wrong patterns.

2) For old records, fetch all result and do it in your programming language. If it is PHP you can use ctype_digit() or is_numeric to check if the field contains only numeric values.

Thiraj Priyadarshana
  • 131
  • 1
  • 1
  • 10