CODEWITHSUNDEEP
sqlregexoracle-database
NDubonix
NDubonix

Reputation: 103

RegExp Find Numbers that have All Same Digits

I am working with an Oracle database and would like to write a REGEXP_LIKE expression that finds any number where all digits are the same, such as '999999999' or '777777777' without specifying the length of the field. Also, I would like it to be able to identify characters as well, such as 'aaaaa'.

I was able to get it working when specifying the field length, by using this:

select * from table1
where regexp_like (field1, '^([0-9a-z])\1\1\1\1\1\1\1\1');

But I would like it to be able to do this for any field length.

If a field contains '7777771', for example, I would not want to see it in the results.

Upvotes: 1

Views: 2711

Answers (2)

CMPS
CMPS

Reputation: 7769

Try this:

^([0-9a-z])\1+$

Live demo

Upvotes: 7

Andy Lester
Andy Lester

Reputation: 93676

You're almost there. You just need to anchor the end of the regex.

^([0-9a-z])\1+$

Upvotes: 6

Related Questions