DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Report help

  1. #1
    Join Date
    Jun 2000
    Location
    GA
    Posts
    43

    Report help

    Hi,

    I'm trying to create a report by selecting the ssn and empid from a table. the problem is that the ssn field are in the form xxx-xx-xxxx, while the empid is in the form xx/xx-xxxx, xx , xx/xx/xxxx or sometimes xx-xx-xxxx. How do I write a query to ignore any of the these characters (/,-,_,) for the ssn and empid fields.
    OCP 8i DBA
    I'm still learning

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Use the REPLACE() function
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    SQL> select * from xyz;
    
    X
    --------------------
    321-45-8282
    321/45/8282
    
    SQL> select translate(x,'123456789-/','123456789') from xyz;
    
    TRANSLATE(X,'1234567
    --------------------
    321458282
    321458282
    Jeff Hunter

  4. #4
    Join Date
    Jun 2000
    Location
    GA
    Posts
    43
    thx
    OCP 8i DBA
    I'm still learning

  5. #5
    Join Date
    Jun 2000
    Location
    GA
    Posts
    43
    Thanks Jeff, I appreciate your prompt assistance
    Last edited by ogbologbo; 02-12-2004 at 09:57 AM.
    OCP 8i DBA
    I'm still learning

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