Solved

PL/SQL - Leading zeros

Posted on 2016-11-09
7
95 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
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 

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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

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.  …
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 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.
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.

696 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