Adib Akale - 8 months ago 38

SQL Question

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.

Source (Stackoverflow)