Re: Best way to find string composed of dup character?

"Dr Drudge" <drdrudge_at_hotmail.com> wrote in message news:748419a0.0402250601.18e767f_at_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
When you say "contains" do you mean only strings like 111111, 999999 etc., or do you also mean 2111111134 ?
Your predicate seems to find 1111111, 91111111, 991111111 etc. - meaning that the last 7 characters should be identical.

If you mean you should catch only 111111,9999999 etc. you could just do :

p1.phone_number !=
rpad(substr(p1.phone_number,1,1),length(p1.phone_number),substr(p1.phone_num ber,1,1))

but I guess you would have thought of that :-)

So if you want that the last 7 characters should be identical your way should probably be OK - not fast, perhaps, but it's not at easy one to get fast :-)

If you allways have just digits in the string and you need to find versions like 21111111134, then a simple but ugly way might be :

translate(p1.phone_number,'0','#') not like '%#######%' and
translate(p1.phone_number,'1','#') not like '%#######%' and
translate(p1.phone_number,'2','#') not like '%#######%' and
translate(p1.phone_number,'3','#') not like '%#######%' and
translate(p1.phone_number,'4','#') not like '%#######%' and
translate(p1.phone_number,'5','#') not like '%#######%' and
translate(p1.phone_number,'6','#') not like '%#######%' and
translate(p1.phone_number,'7','#') not like '%#######%' and
translate(p1.phone_number,'8','#') not like '%#######%' and
translate(p1.phone_number,'9','#') not like '%#######%'

(The number of #'s determine how many consecutive identical digits you would like to be present in the string)

It's ugly and not fast - but it'll find them allright :-)

/KiBeHa Received on Wed Feb 25 2004 - 08:55:01 CST