String search in large table [message #39623] |
Sat, 27 July 2002 11:26 |
Tracy Veara
Messages: 2 Registered: July 2002
|
Junior Member |
|
|
Hi guys,
I appreciate any help you can give me here.
My supervisor has asked me to query a very large (several million rows) table for some specific information. I am to return all records in the table where any values in the SEC_ID column contain three or more repeating characters. I need to be able to search for ANY alphanumeric or special characters at any location in the string. Obviously the workaround is to specify criteria for every possible combination of three characters available on a standard keyboard, but I really want to stretch my skills by learning to do this programmatically. Can anyone assist me? Thanks.
Regards,
Tracy Veara
|
|
|
Re: String search in large table [message #39624 is a reply to message #39623] |
Sat, 27 July 2002 21:08 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Tracy, I can't test this right now, but this might get you started in the right direction. Someone else might chip in with a better algorithm.
create or replace function f_contains_repeating_char
(p_string in varchar2, p_count in pls_integer)
return pls_integer
is
v_length pls_integer := nvl(length(p_string), 0);
v_count pls_integer := least(v_length, 1);
begin
if v_length >= p_count then
for i in 2..v_length loop
if substr(p_string, i, 1) = substr(p_string, i - 1, 1) then
v_count := v_count + 1;
if v_count = p_count then
exit;
end if;
else
v_count := 1;
end if;
end loop;
end if;
return (v_count);
end;
/
|
|
|
|