Adib Akale Adib Akale - 1 month ago 11
SQL Question

Trying to match patterns and repeating numbers in a field

I have a field [SSN] that stores social sec numbers. A lot of the records have a dummy value there and I am trying to find these in a Where statement. I am trying to find:


  • rows that have repeating numbers within a range 0-9.

  • rows that have numbers in this range that repeat 5 times



Examples of what I am trying to find:

00000
11111
22222
333333
444444
555555
666666
777777


I am using TOAD for Oracle at the moment to run the query.

Answer

You can find these patterns using a regular expression with a back reference. I think this will do the trick:

where regexp_like(ssn, '([0-9])(\1){4}')

That is, find a digit and then determine if it is repeated 4 or more times.