sqlplus matching

Hi Friends,

I have table EMP with column empname and another
LOANS_TABLE with column fullname.

EMP
======
EMPNAME
-----------
TOM WELLING
SCOTT WILSON


LOANS_TABLE
===========
FULLNAME
------------
WELLING, TOM

I want to list/match the employees with loans, eventhough lastname is
entered first in the loans table. In this case TOM WELLING is same
as WELLING, TOM

Can you help me please...

Thanks a lot

update your data first

Code:

---

SQL> select * from xyz;

X
--------------------
elmer fudd

SQL> select * from abc;

Y
--------------------
fudd, elmer

SQL> edit
Wrote file afiedt.buf

  1  select xyz.x, abc.y
  2  from xyz, abc
  3  where trim(xyz.x) =
  4    trim(substr(y, instr(y,',')+1,
  5        length(y)) || ' ' ||
  6*      substr(y,1, instr(y,',')-1))
SQL> /

X                    Y
-------------------- --------------------
elmer fudd          fudd, elmer

---

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

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

hi "hun"...would it be slower or faster if i write it this way?

1 select A.x, B.y
2 from xyz A, abc B
3 where trim(A.x) =
4 trim(substr(B.y, instr(B.y,',')+1,
5 length(B.y)) || ' ' ||
6* substr(B.y,1, instr(B.y,',')-1))
SQL> /

What's different?

Thanks hun.....but there are still some records that can not be hit...

I want something that uses the "like" function... as in:

select x.empname, y.fullname from EMP x, LOANS_TABLE y
where y.fullname like '%TOM%' and y.fullname like '%WELLING%');

Can u help me split the empname to be similar to the above form?


Thanks again

Hi hun...fullname may contains more than two words like

LOANS_TABLE
===========

FULLNAME
-----------------
JOSE PROTACIO RIZAL MERCADO Y REALONDA

try regular expressions if you are using 10g

thanks pando....but is "regular expression" a new feature in 10g?