-
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
-
-
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> /
-
-
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?