Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    14

    Arrow Unanswered: Finding repeated numbers

    Hi all,

    I am creating a Stored Procedure for finding the numbers.
    my input is '12-34-65-876-236' & '98-236-765-12-34'
    '23-98-12-23-43' ....etc

    From this one i need output as how many times the number in first input is matched with second input.
    out put like
    3 numbers are matched in first number .they r 12 , 34 , 236
    1 number matched in second number .they r 12

    two inputs are varchar2(50).

    I am creating as stored procedure.facing difficulty in that.
    waiting for replies.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Finding repeated numbers

    Originally posted by srikanthdba
    Hi all,

    I am creating a Stored Procedure for finding the numbers.
    my input is '12-34-65-876-236' & '98-236-765-12-34'
    '23-98-12-23-43' ....etc

    From this one i need output as how many times the number in first input is matched with second input.
    out put like
    3 numbers are matched in first number .they r 12 , 34 , 236
    1 number matched in second number .they r 12

    two inputs are varchar2(50).

    I am creating as stored procedure.facing difficulty in that.
    waiting for replies.
    Two steps required:

    1) break up the 2 input strings at the '-' delimiters. I have some handy code to do that (see below). It puts the individual numbers (or whatever) into index-by tables.

    2) compare the 2 sets of values. If these are in index-by tables, you can simply loop through and compare.

    Code:
    CREATE OR REPLACE PACKAGE parse AS
    
      TYPE varchar2_table IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
    
      PROCEDURE delimstring_to_table
        ( p_delimstring IN  VARCHAR2
        , p_table       OUT varchar2_table
        , p_nfields     OUT INTEGER
        , p_delim       IN  VARCHAR2 DEFAULT ','
        );
    
    END parse;
    /
    CREATE OR REPLACE PACKAGE BODY parse AS
    
      PROCEDURE delimstring_to_table
        ( p_delimstring IN  VARCHAR2
        , p_table       OUT varchar2_table
        , p_nfields     OUT INTEGER
        , p_delim       IN  VARCHAR2 DEFAULT ','
        )
      IS
        v_string   VARCHAR2(32767) := p_delimstring;
        v_nfields  PLS_INTEGER := 1;
        v_table    varchar2_table;
        v_delimpos PLS_INTEGER := INSTR(p_delimstring, p_delim);
        v_delimlen PLS_INTEGER := LENGTH(p_delim);
      BEGIN
        WHILE v_delimpos > 0
        LOOP
          v_table(v_nfields) := SUBSTR(v_string,1,v_delimpos-1);
          v_string := SUBSTR(v_string,v_delimpos+v_delimlen);
          v_nfields := v_nfields+1;
          v_delimpos := INSTR(v_string, p_delim);
        END LOOP;
        v_table(v_nfields) := v_string;
        p_table := v_table;
        p_nfields := v_nfields;
      END delimstring_to_table;
    
    END parse;
    /

  3. #3
    Join Date
    Aug 2003
    Posts
    14

    Re: Finding repeated numbers

    Originally posted by andrewst
    Two steps required:

    1) break up the 2 input strings at the '-' delimiters. I have some handy code to do that (see below). It puts the individual numbers (or whatever) into index-by tables.

    2) compare the 2 sets of values. If these are in index-by tables, you can simply loop through and compare.

    Code:
    CREATE OR REPLACE PACKAGE parse AS
    
      TYPE varchar2_table IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
    
      PROCEDURE delimstring_to_table
        ( p_delimstring IN  VARCHAR2
        , p_table       OUT varchar2_table
        , p_nfields     OUT INTEGER
        , p_delim       IN  VARCHAR2 DEFAULT ','
        );
    
    END parse;
    /
    CREATE OR REPLACE PACKAGE BODY parse AS
    
      PROCEDURE delimstring_to_table
        ( p_delimstring IN  VARCHAR2
        , p_table       OUT varchar2_table
        , p_nfields     OUT INTEGER
        , p_delim       IN  VARCHAR2 DEFAULT ','
        )
      IS
        v_string   VARCHAR2(32767) := p_delimstring;
        v_nfields  PLS_INTEGER := 1;
        v_table    varchar2_table;
        v_delimpos PLS_INTEGER := INSTR(p_delimstring, p_delim);
        v_delimlen PLS_INTEGER := LENGTH(p_delim);
      BEGIN
        WHILE v_delimpos > 0
        LOOP
          v_table(v_nfields) := SUBSTR(v_string,1,v_delimpos-1);
          v_string := SUBSTR(v_string,v_delimpos+v_delimlen);
          v_nfields := v_nfields+1;
          v_delimpos := INSTR(v_string, p_delim);
        END LOOP;
        v_table(v_nfields) := v_string;
        p_table := v_table;
        p_nfields := v_nfields;
      END delimstring_to_table;
    
    END parse;
    /


    Hi Thanks for your reply.
    Still I am facing Problem.
    I am dount that whether to create index-by tables.or what?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Finding repeated numbers

    Originally posted by srikanthdba
    Hi Thanks for your reply.
    Still I am facing Problem.
    I am dount that whether to create index-by tables.or what?
    My suggestion requires index-by tables, yes.

    Code:
    SQL> DECLARE
      2    string1 varchar2(2000) := '12-34-56';
      3    ibt1 parse.varchar2_table;
      4    nfields integer;
      5  BEGIN
      6    parse.delimstring_to_table( string1, ibt1, nfields, '-' );
      7    for i in 1..nfields loop
      8      dbms_output.put_line( ibt1(i) );
      9    end loop;
     10* END;
    SQL> /
    12
    34
    56
    
    PL/SQL procedure successfully completed.

  5. #5
    Join Date
    Aug 2003
    Posts
    14
    Hi all,
    Still I am facing proble for comparing the data...how many numbers are matched and what are they.
    Waiting for reply

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by srikanthdba
    Hi all,
    Still I am facing proble for comparing the data...how many numbers are matched and what are they.
    Waiting for reply
    Sorry I thought maybe you could handle that part yourself. To compare 2 arrays:

    Code:
    FOR i IN 1..array1.COUNT LOOP
      FOR j IN 1..array2.COUNT LOOP
        IF array1(i) = array2(j) THEN
          -- Match found, write out array1(i) value etc.
        END IF;
      END LOOP;
    END LOOP;
    Pretty simple really.

  7. #7
    Join Date
    Aug 2003
    Posts
    14
    Hi,
    Thanks for your reply.But my problem is I am selecting
    one number('12-34-65-75-86') from one table on the basis of primary key.
    result is :'12-34-65-75-86'

    Later i am selecting one column from the table2 returning 1000+rows like '45-64-23-53-24','34-64-12-34-76'..........etc(1000+rows).

    Now i have to compare the first number what i got from first table with alll numbers from the second table.....all the returning numbers are varchar....I am trying for this last 4 days.but I could n't solve it...

    Result should be...like...
    5 numbers matched are 147
    4 numbers matched are 154
    2 numbers matched are 876
    1 number matched are 23456


    please send me stored procedure for that....I am working on it since 4 days...

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by srikanthdba
    Hi,
    Thanks for your reply.But my problem is I am selecting
    one number('12-34-65-75-86') from one table on the basis of primary key.
    result is :'12-34-65-75-86'

    Later i am selecting one column from the table2 returning 1000+rows like '45-64-23-53-24','34-64-12-34-76'..........etc(1000+rows).

    Now i have to compare the first number what i got from first table with alll numbers from the second table.....all the returning numbers are varchar....I am trying for this last 4 days.but I could n't solve it...

    Result should be...like...
    5 numbers matched are 147
    4 numbers matched are 154
    2 numbers matched are 876
    1 number matched are 23456


    please send me stored procedure for that....I am working on it since 4 days...
    I don't have the time to write all the code for you. The pseudo code would be something like:

    Code:
    get table1 value e.g. '12-34-65-75-86'
    use parse routine to break into array1 with values array1(1)=12, array1(2)=34, etc.
    for each value in table2
      use parse routine to break this table2 value into array2
      use nested FOR loops to compare each array2 value with each array1 value
        when match found with value e.g. v=12 then 
          if array3.exists(v) then
            array3(v) := array3(v)+1;
          else
            array3(v) := 1;
          end if;
    Array3 then contains the results in this form:
    array3(12) = 1
    array3(147) = 5
    array3(154) = 4
    etc.

    You can then use array3.first, next and last to print this info out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •