Solved

PL/SQL - Leading zeros

Posted on 2016-11-09
7
41 Views
Last Modified: 2016-11-10
I have a table that I am trying to concatenate the alpha, next_number, suffix, and length fields together with leading zeros.  I cannot get the length to work correctly.  If I put 10 in the field "LENGTH" then it work fine.  However, I want the user to be able to define the length via a table.

Table Field Below:
ALPHA
NEXT_NUM
SUFFIX
LENGTH

SELECT LPAD(ALPHA||NEXT_NUM||SUFFIX, LENGTH, '0')
FROM   NEXT_NUMBER

Open in new window


So the above concatenate the alpha, next_num, and suffix together.  The leading zeros is the length so for this example, say 10.  If my alpha, next_num, and suffix is 12345, it should display as 0000012345 with 0 leading zeros since my length is 10.
0
Comment
Question by:holemania
  • 3
  • 3
7 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41880969
First:  PL/SQL is Oracle's Procedural Language.  SQL is just SQL.

What doesn't work?

Using the code below I get:  0000012345


drop table tab1 purge;
create table tab1(
ALPHA number,
NEXT_NUM number,
SUFFIX number,
LENGTH number);

insert into tab1 values(12,34,5,10);
commit;

SELECT LPAD(ALPHA||NEXT_NUM||SUFFIX, LENGTH, '0')
FROM   tab1;

Open in new window

1
 
LVL 31

Expert Comment

by:awking00
ID: 41881104
What are the data types for those fields?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41881434
Just thought of something:
If you take the output and put it back into a numeric data type, you will lose the leading zeros.  LPAD returns one of the character data types like varchar2, clob, etc.. you need to keep it as such.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:holemania
ID: 41882285
Thanks slightwv.  Found out the length (6 character) wasn't long enough when my data is 7 characters long.

Reason why I kept mentioning PL/SQL is because this is the platform I am working with is Oracle.  Sometimes people tried giving me MS SQL answers and find out it's Oracle platform, and syntax may be a bit different.  Example, instead of "Print" for output it's "dbms_output.put_line" instead for oracle.  Just like your example, using "||" to concatenate is different than "+" for MS SQL.  So I just want to clarify that.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41882294
>>Reason why I kept mentioning PL/SQL is because this is the platform I am working with is Oracle

Again, in Oracle PL/SQL is the Procedural Language used to code stored functions and procedures.  SQL is just called SQL.

Just say you want it for Oracle.  If you mention PL/SQL we think you want actual code not straight SQL.
0
 

Author Comment

by:holemania
ID: 41882302
Sure, will keep that in mind just to mention Oracle and if sql can accomplish similar task great.   If not and PL/SQL is required, then person assisting can provide example.

Thanks again for all your help.
0
 

Author Closing Comment

by:holemania
ID: 41882308
Thank you.  My script was working, but didn't realized that my length was only 6 and my value is 7 characters long.  

Your example helped find me discovered that.  Thanks again.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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.

759 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

20 Experts available now in Live!

Get 1:1 Help Now