1. Registered User
Join Date
Aug 2003
Posts
14

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. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

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. Registered User
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;
/```

Still I am facing Problem.
I am dount that whether to create index-by tables.or what?

4. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

Re: Finding repeated numbers

Originally posted by srikanthdba
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. Registered User
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.

6. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Originally posted by srikanthdba
Hi all,
Still I am facing proble for comparing the data...how many numbers are matched and what are they.
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. Registered User
Join Date
Aug 2003
Posts
14
Hi,
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. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Originally posted by srikanthdba
Hi,
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
•