PL/SQL LPAD Function

Posted on 2009-05-01
Medium Priority
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
  • 3
LVL 74

Expert Comment

ID: 24281986
select substr(field,1,5) from your_table
LVL 74

Expert Comment

ID: 24282038
with the alias...

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

Author Comment

ID: 24282082
But where exactly do I put this?

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

Accepted Solution

sdstuber earned 1000 total points
ID: 24282146
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

862 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