Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003

    Wink Unanswered: Truncating part of a zip code

    Sorry for the remedial question (after searching here and google and my pl/sql books for 30 minutes)....

    How do you truncate a record returned from a query?

    I am pulling a zip code from the database and want to get rid of the hyphen and last 4 digits. I.E. 60691-4467 ---> 60691

    I don't know why I can't find anything on something this rediculous. If anyone has any helpful links they can give me for good reference for this kind of silly stuff I'd appreciate it as well.


  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: Truncating part of a zip code

    The exact answer depends on your DBMS, but will be similar to this Oracle solution:

    1) Use the INSTR function to find the hyphen:
    INSTR( zip_code, '-' )
    This returns 6 in your example, and 0 if there is no hyphen

    2) Use the SUBSTR function to get just the part before the hyphen:

    To neatly handle the case where the hyphen is absent, you could do this:

    SELECT SUBSTR( zip_code, 1, INSTR( zip_code||'-', '-') - 1 )
    FROM ...

    I think INSTR may be called CONTAINS, and SUBSTR called SUBSTRING in SQL Server.

  3. #3
    Join Date
    Jan 2003

    Re: Truncating part of a zip code

    Sorry Tony.... I am using 8i. And thank you for the suggestions!!

  4. #4
    Join Date
    Jan 2003
    Can you use the LEFT function??

    LEFT ( character_expression , integer_expression )


    would give you what you want.

Posting Permissions

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