CODEWITHSUNDEEP
sqlregexoracle-database
ganezdragon
ganezdragon

Reputation: 189

regex - match exactly 10 digits with atleast one symbol or spaces between them

I'm trying to write a query in oracle sql to get rows which has invalid 10 digit numbers, ie with other symbols in between them.

For example:

(111) 111-1111       #10 digit number with some symbols and spaces in between
111-111-1111         
(111)111-1111
111)111-1111
(111) 11    1-1111

ie, It should match exactly 10 digit numbers which are non consecutive because it has some symbols in it.

So it should not match the following example:

111          #consecutive 3 digit number
11   1       #3 digit number with spaces
11-1         #3 digit number with symbol in between
1111111111   #consective 10 digit number

And I'm using REGEXP_LIKE, something like this

select * from table where REGEXP_LIKE(column, ?)

Any help is much appreciated. Thanks.

Upvotes: 0

Views: 2870

Answers (2)

user5683823
user5683823

Reputation:

So: the string should have length greater than 10, and the total number of digits must be exactly 10. This can be done without regular expressions (which should make it faster):

... where length(str) > 10 and
          length(str) = 10 + length(translate(str, 'z0123456789', 'z'))

translate will translate the letter z to itself and all the other characters (digits) to nothing. Having to include the z is annoying, but unavoidable; translate will return NULL if any of its arguments is NULL. The second condition says the length of the input str is exactly 10 more than the length of the string with all digits removed - so there are exactly 10 digits.

Upvotes: 1

trincot
trincot

Reputation: 350310

You could use a combination of a regex and length; the latter to exclude a pure 10-digit number without other characters:

regexp_like(col, '^[ .()-]*(\d[ .()-]*){10}$') and length(col) > 10

In the [.()-] class you would list all the characters that you would allow as symbols among the digits. Note that - needs to be the last in that list or else be escaped.

If you would allow any non-digit to occur among the 10 digits, you can use \D:

regexp_like(col, '^\D*(\d\D*){10}$') and length(col) > 10

Upvotes: 2

Related Questions