Solved

PL/SQL - Leading zeros

Posted on 2016-11-09
7
88 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 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 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

726 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