| string pattern matchin in SQL [message #373269] |
Sat, 07 April 2001 12:28  |
Ben
Messages: 48
Registered: January 2000
|
Member |
|
|
Hi,
I have two attributes of type VARCHAR2. I want to know if they have a matching substring. How can I do it with SQL. Here is an example:
Name='John, George, Lisa'
Members='Frank, Lisa, David'
I want as output from SQL: 'Lisa'
Thanks
Ben
|
|
|
|
| Re: string pattern matchin in SQL [message #373282 is a reply to message #373269] |
Mon, 09 April 2001 16:39  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
As I see it, you must tokenize one of the strings, and then you can use instr() of each token against the comparison string to find matches.
CREATE OR REPLACE PROCEDURE COMMA_SAMPLE AS
mytable DBMS_UTILITY.uncl_array;
mylist VARCHAR2(80);
mytable_count NUMBER;
BEGIN
mylist := 'Alex, Donna, Hope, Jose, Judy, Julia, Nancy, Paul, Sandy';
DBMS_OUTPUT.PUT_LINE('MYLIST: ' || mylist);
DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable);
mylist := 'Empty.';
DBMS_OUTPUT.PUT_LINE('MYTABLE: ');
DBMS_OUTPUT.PUT_LINE('---------------------------');
FOR item IN 1..mytable_count LOOP
DBMS_OUTPUT.PUT_LINE(mytable(item));
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
DBMS_UTILITY.TABLE_TO_COMMA(mytable, mytable_count, mylist);
DBMS_OUTPUT.PUT_LINE('MYLIST: ' || mylist);
END;
/
begin
comma_sample;
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
