sqlplus matching

[JMac]

We'll be using SOUNDEX next!

[davey23uk]

Originally posted by kris123
thanks pando....but is "regular expression" a new feature in 10g?

yes it is, althought you have some very limited functionality in owa_pattern in 9i

[marist89]

Originally posted by kris123
Thanks to you my role-models ...im sorry i cant update the other apps tables coz its owned by other group and we have read only permission

Look, you're trying to use technology to get around bad data. If the tables you don't own were designed by someone with half a brain they didn't use name as the primary key. You should use their primary key in your table so you don't have these problems.

If the person didn't have half a brain, then the format of your data should match their data. Then you wouldn't have to kludge this thing to get a decent join. Fact is, if there is no pattern to their data you can't write a function to join it.

[reydp]

I have to agree with the guys who replied your post,
but I know how difficult to propose changes to management here in our place.
I have a dirty but somehow nifty approach to solve your problem.
As what Jeff mentioned it is import to have keys in both tables,
I don't want to explain the script 'cause it's so basic. Your last step will be on how to tackle the records from both tables that wasn't included in the view if there is.
Hope this can help Ate.
SCRIPT:

SQL> create table emp(empname varchar2(35), emp_id number(4) not null);

Table created.

SQL> create table loans_table(fullname varchar2(30), loan_id number(4) not null)
SQL> /

Table created.

SQL> insert into emp values('TOM WELLING', 100);

1 row created.

SQL> ED
Wrote file afiedt.buf

1* insert into emp values('SCOTT WILSON', 101)
SQL> /

1 row created.

SQL> insert into LOANS_TABLE values('WELLING, TOM',2001);

1 row created.

SQL> ed
Wrote file afiedt.buf

1* insert into LOANS_TABLE values('WILSON, SCOTT',2002)
SQL> /

1 row created.

SQL> COMMIT;

SQL> SELECT * FROM EMP;

EMPNAME EMP_ID
----------------------------------- ----------
TOM WELLING 100
SCOTT WILSON 101

SQL> SELECT * FROM LOANS_TABLE
2 ;

FULLNAME LOAN_ID
------------------------------ ----------
WELLING, TOM 2001
WILSON, SCOTT 2002


CREATE VIEW LOAN_EMP_V AS SELECT LTRIM(SUBSTR(FULLNAME,INSTR(FULLNAME,',',1)+1,LENGTH(FULLNAME)
SUBSTR(FULLNAME,1, INSTR(FULLNAME,',',1)-1) FULLNAME, LOAN_ID
FROM LOANS_TABLE
/

SQL> SELECT * FROM LOAN_EMP_V;

FULLNAME LOAN_ID
------------------------------------------------------------- ----------
TOM WELLING 2001
SCOTT WILSON 2002


SQL> col fullname format a30
SQL> l
1 select emp_id, fullname, loan_id
2 from emp, loan_emp_v
3* where empname = fullname
SQL> /

EMP_ID FULLNAME LOAN_ID
---------- ------------------------------ ----------
101 SCOTT WILSON 2002
100 TOM WELLING 2001

[kris123]

Thanks to all of you my helpful kuya....honestly i have to analyze first what all you have written as it hardly sync into my brain

Just want to add some info about the simple function pattern example:

Table1:
=======
name1
----------------
TOM WELLING
TOM CRUISE
TOM PANGIT
PANGIT

Table2:
=======
name2
-------------------
WELLING CRUISE TOM POGI
IKAW AY PANGIT

Only table2 will serve as the source table to match with, while
table one is the transaction table.

Rows that matched in table1 are TOM WELLING, TOM CRUISE, and PANGIT coz all words are contained in rows in table2.

Thanks honieluv

[reydp]

Originally posted by kris123
Just want to add some info about the simple function pattern example:

Table1:
=======
name1
----------------
TOM WELLING
TOM CRUISE
TOM PANGIT
PANGIT

Table2:
=======
name2
-------------------
WELLING CRUISE TOM POGI
IKAW AY PANGIT

Only table2 will serve as the source table to match with, while
table one is the transaction table.

Rows that matched in table1 are TOM WELLING, TOM CRUISE, and PANGIT coz all words are contained in rows in table2.

Thanks honieluv

Maybe you have more info to add?
In this case take it from Jeff's word:

Can you say "full table scan baby"? Also, this method has tons of potential for error... As pando says, make your data clean.