| Most qualified match [message #374426] |
Mon, 11 June 2001 10:46  |
Ivo Beckers
Messages: 4
Registered: June 2001
|
Junior Member |
|
|
|
I'd like to perform a query which provides "the most qualified match" as a result. The data of the field that is used to perform the query is fully specified (like abcdefg). The data of the field in the table itself only contains prefixes (like abc*, abcd*). In this example, the result of querying with "abcdefg" would be the record that contains abcd*.
|
|
|
|
| Re: Most qualified match [message #374428 is a reply to message #374426] |
Mon, 11 June 2001 14:15  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
I would set up a stored function like this (note that this does exact case match and presumes an asterisk at the end of the prefix as per your example):
FUNCTION sf_prefixFinder (i_inputString IN VARCHAR2) RETURN VARCHAR2
IS
l_prefix VARCHAR2(200) := NULL;
--
BEGIN
--
FOR i IN REVERSE 1..LENGTH(i_inputString)
LOOP
EXIT WHEN l_prefix IS NOT NULL;
BEGIN
SELECT prfx
INTO l_prefix
FROM mytest
WHERE SUBSTR(prfx,1,i) = SUBSTR(i_inputString,1,i)
AND LENGTH(prfx)-1 <= LENGTH(i_inputString);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
END LOOP;
RETURN l_prefix;
END;
Here is the test of my example:
SQL> select sf_prefixFinder('abcdefgh') from dual;
SF_PREFIXFINDER('ABCDEFGH')
---------------------------
abcd*
SQL> select * from mytest;
PRFX
--------------------
abc*
abcd*
acdbef*
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
