DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: sqlplus matching

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    update your data first

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter

  4. #4
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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

  5. #5
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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> /

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What's different?
    Jeff Hunter

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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

  8. #8
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Hi hun...fullname may contains more than two words like

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

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

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    try regular expressions if you are using 10g

  10. #10
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    thanks pando....but is "regular expression" a new feature in 10g?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width