PL/SQL LPAD Function

Posted on 2009-05-01
Last Modified: 2013-12-07
I have a select all statement, but I want one of my fields (a zip code field) to be formated to pull in with only the first 5 digits.  Where and how can I stipulate that?

FROM <table name>

LPAD("field",5) as "Zip_Code"  (not sure where to put this)
Question by:Luis5011
    LVL 73

    Expert Comment

    select substr(field,1,5) from your_table
    LVL 73

    Expert Comment

    with the alias...

    select substr(field,1,5)  "Zip_Code" from your_table

    Author Comment

    But where exactly do I put this?

    Not sure how to add this to a select all function.
    LVL 73

    Accepted Solution

    you can put it before or after the *

    select  your_table.*, substr(field,1,5)  "Zip_Code" from your_table
    select  substr(field,1,5)  "Zip_Code", your_table.* from your_table

    but, as you see in my examples, you must alias the * with the source, in this case "your_table"

    note, since * is all columns, you will have both FIELD and "Zip_Code" columns in your output.

    if you are trying to format a specific column from within the *, you can't.
    so, if you want every column but you want to do something special to the zip code column you'll have to list them all.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Update a field datetime 3 30
    Mysql vs Oracle 10 105
    Oracle SQL Select Statement 19 43
    performance tunning sql insert - challenging one 2 20
    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now