Best way to find string composed of dup character?

From: Dr Drudge <drdrudge_at_hotmail.com>
Date: 25 Feb 2004 06:01:29 -0800
Message-ID: <748419a0.0402250601.18e767f@posting.google.com>

I need to be able to determine whether a varchar2 column contains a string of dup characters. Specifically, I need to weed out (or alternatively find) phone numbers like: 1111111, 9999999999, etc. Here is what I came up with for use in a where predicate: ...
substr(p1.phone_number,-(least(7,length(p1.phone_number)))) != rpad(substr(p1.phone_number,-1,1),least(7,length(p1.phone_number)),

                                         substr(p1.phone_number,-1,1))

Obviousely "P1" is the phone_number table. I used LEAST to check up to seven digits- not sure if that's helpng or hurting though.

Any comments?
TIA Received on Wed Feb 25 2004 - 08:01:29 CST