?
Solved

PL/SQL - Leading zeros

Posted on 2016-11-09
7
Medium Priority
?
101 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 32

Expert Comment

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

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
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 

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 77

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

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

801 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