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

Thanks for the reply- comments in-line:
>
> 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.
>

Yes- I want only the last seven tested. Sorry about mis-stating the requirement (I wrote the query a few weeks ago and forgot that "detail").

> 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 :-)
>

Yes, I know it works but sometimes I like to do a sanity check- now and then I'll miss the obvious and overcompilcate the thing.  

> 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 :-)
>

Good sggestion but not what I'm looking for. I should have specified that they're phone numbers and sometimes the area code is entered but the rest are dummy digits, like: 2120000000. Received on Thu Feb 26 2004 - 13:17:07 CST