Home » SQL & PL/SQL » SQL & PL/SQL » String search in large table
String search in large table [message #39623] Sat, 27 July 2002 11:26 Go to next message
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 Go to previous messageGo to next message
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;
/
Re: String search in large table [message #39653 is a reply to message #39623] Wed, 31 July 2002 05:05 Go to previous message
venkatesh
Messages: 72
Registered: August 2000
Member
hi tracy

can u still be articulate about ur problem....
u can contact me at venkateshbabutk@yahoo.com or in this forum itself....

venki
Previous Topic: Loops and varchar parsing
Next Topic: Creating a temp table and unique numbering
Goto Forum:
  


Current Time: Fri Mar 29 02:10:26 CDT 2024