PL/SQL - Leading zeros

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.
holemaniaAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
awking00Commented:
What are the data types for those fields?
0
 
slightwv (䄆 Netminder) Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
holemaniaAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
holemaniaAuthor Commented:
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
 
holemaniaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.