0

I am trying to make a regexp for mobile number field such that, it would not accept the following type of inputs:

  1. 0000000000
  2. 1111111111
  3. 2222222222

like wise continuous same 10 digits. How to avoid accepting continuous similar ten digits as input for mobile number field?

Michał Perłakowski
  • 88,409
  • 26
  • 156
  • 177
Razal Muhammed
  • 21
  • 1
  • 1
    Sounds like something you may want to do _before_ inserting into the database. Is it possible to do in the application so that the user can get better feedback than an SQL error? – Joachim Isaksson Jan 24 '16 at 08:05
  • What langage or tool? – Toto Jan 24 '16 at 10:57
  • Is this a particular problem with your users? Are you giving them the option not to enter in a phone number? Are you validating the phone number by calling/texting them? If you put this check in along with others are they just going to put in a number that looks like a valid phone number but not be theirs – Ed Heal Jan 24 '16 at 13:09

2 Answers2

2

A simple regex solution would be to capture the first digit and check if it's repeated 9 times and then take necessary actions. As you haven't mentioned any other tag I will leave it up to you to apply necessary actions.

Regex: /(\d)\1{9}/g Captures the first digit and if it's repeated 9 times then it matches pattern.

Regex101 Demo

1

You can use NOT REGEXP_LIKE(MOBILE_NO,'(.)\1{9,}') like below to suppress the unwanted input. But as suggested by Joachim, it is better to avoid it from front end if possible. Check this query and let me know if you get any issue.

WITH TBL(MOBILE_NO) AS
( SELECT '1111111111' FROM DUAL UNION
 SELECT '1234444444' FROM DUAL UNION
 SELECT '2222222222' FROM DUAL
 )
SELECT * FROM TBL
where NOT REGEXP_LIKE(MOBILE_NO,'(\d)\1{9,}')

This will give output as 1234444444 and will skip other mobile_no, where there are 10 consecutive same numbers.

Referred this answer to get this.

Community
  • 1
  • 1
Utsav
  • 7,914
  • 2
  • 17
  • 38
  • Note that `.` matches alphabets too. It would fail for patterns like `aaa` and so on. Use only `\d` when numeric data is to be tested. –  Jan 24 '16 at 08:46