Home » SQL & PL/SQL » SQL & PL/SQL » LOOP to Match a record (merged)
LOOP to Match a record (merged) [message #227600] Wed, 28 March 2007 14:56 Go to next message
rudorathod
Messages: 14
Registered: October 2006
Junior Member
Hi
I have a one dimensional table.
The records in the Table are
ABC1234
ABC123
ABCD1234
ABCD123

I have a record that I have to check against the above table
the record could be ABC1234556

so I start checking ABCD1234556 --> no record found,
so I substring it to ABCD123455 --> no record found,
so I substring it to ABCD12345 --> no record found,
so I substring it to ABCD1234 --> record found
come out of the loop

i want to make it a procedure that will help me loop thru the record
procedure can take the record as a paramater
Re: LOOP to Match a record [message #227612 is a reply to message #227600] Wed, 28 March 2007 15:30 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://sheikyerbouti.developpez.com/collections/collections.htm

Take a look at "pl/sql associative arrays" specifically of type varchar2.

you can also query the array directly...
create or replace type C10_TTyp as table of varchar2(10);

select * from(
SELECT column_value FROM TABLE (C10_TTyp ('ABC1234','ABC123','ABCD1234','ABCD123')) 
where instr('ABC1234556', column_value)=1
order by length(replace('ABC1234556', column_value))
) where rownum =1;

COLUMN_VALUE
ABC1234
Re: LOOP to Match a record [message #227620 is a reply to message #227612] Wed, 28 March 2007 15:57 Go to previous messageGo to next message
rudorathod
Messages: 14
Registered: October 2006
Junior Member
IS there a way where we can stop looking if the length of the string becomes 4. in other words we do not want the records with records of length 1 or 2 or 3 or 4

Re: LOOP to Match a record [message #227624 is a reply to message #227600] Wed, 28 March 2007 16:10 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> IS there a way where we can stop looking if the length of the string becomes 4. in other words we do not want the records with records of length 1 or 2 or 3 or 4
yes, add another phrase to the WHERE clause to check for that condition!
substr in a loop [message #227817 is a reply to message #227600] Thu, 29 March 2007 08:45 Go to previous messageGo to next message
rudorathod
Messages: 14
Registered: October 2006
Junior Member
I have a table with records

create table record( col1 varchar2(20));

insert into table values ('ABCD1234');
insert into table values ('ABCD123');
insert into table values ('ABCD12');
insert into table values ('ABCDE');

ABCD1234
ABCD123
ABCD12
ABCDE.

I have an incoming record ABCD123ABC (passed as a parameter to fun/proc)

I want to write a function/procedure to use the substr function in a loop so that when I match incoming record "ABCD123ABC" to the list in the table. it should find me the exact match. If it does not match then it should substr it to ABCD123AB and start searching, if no match then substr to ABCD123A and search, if no macth then substr to ABCD123. once matched, I want the record .

Re: substr in a loop [message #227822 is a reply to message #227817] Thu, 29 March 2007 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Please do NOT multi-post
http://www.orafaq.com/forum/t/79158/74940/
Re: substr in a loop [message #227824 is a reply to message #227822] Thu, 29 March 2007 09:25 Go to previous messageGo to next message
rudorathod
Messages: 14
Registered: October 2006
Junior Member
I will honor your concern on my future posts.

Thank you
Re: substr in a loop [message #228370 is a reply to message #227817] Mon, 02 April 2007 13:39 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you really want to do it in PL/SQL?
You can do it with a SQL statement:
SQL> var t varchar2(20)
SQL> exec :t := 'ABCD1234556';

PL/SQL procedure successfully completed.

SQL> select col1
  2  from ( select col1
  3         from record
  4         where col1 = substr(:t,1,length(col1))
  5         order by length(col1) desc )
  6  where rownum = 1
  7  /
COL1
--------------------
ABCD1234

1 row selected.

Regards
Michel
Previous Topic: Query Help
Next Topic: How to Delete Multiple tables in single query
Goto Forum:
  


Current Time: Fri Dec 02 22:43:52 CST 2016

Total time taken to generate the page: 0.08641 seconds