Results 1 to 5 of 5

Thread: Report format

  1. #1
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21

    Unanswered: Report format

    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.

    Thanks
    It pays to share!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    TRANSLATE(SSN,'01234567890(/,-_)','01234567890')

  3. #3
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21
    thx
    It pays to share!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    While the non-numeric characters get removed,
    I'm not sure the results are what you want or desire.
    AFAIK, below is a specific case implementation for your solution.

    select translate('123-45-6789','-','123-45-6789') from dual;

    TRANSLATE('
    -----------
    12314516789


    select translate('123-45-6789','0123456789-','0123456789') from dual

    TRANSLATE
    ---------
    123456789

    HTH & YMMV
    HAND!

  5. #5
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21
    Thanks, that's exactly what I'm trying to achieve.
    It pays to share!

Posting Permissions

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