Oracle to_char

In oracle
SELECT TO_CHAR('01110' + 1) FROM dual;

How can I get the  answer as 01111
Tanuja KadamProgrmmer AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
SELECT LPAD(TO_CHAR(TO_NUMBER('01110') + 1),5,'0') FROM dual
Tanuja KadamProgrmmer AnalystAuthor Commented:
I am trying to write a select statement as below,
Where I need to get a middle part of altid and add 1 to it.
It's not always leading with 1 zero, sometimes it has leading 1 zero or multiple
how can I keep the leading zeros and add 1 to it.
SharathData EngineerCommented:
Can you provide some more examples?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Abhimanyu SuriDatabase EngineerCommented:
If I have understood correctly, this may help

SELECT DECODE (SUBSTR ('01110', LENGTH ('01110')),
               0, CONCAT (SUBSTR ('01110', 1, LENGTH ('01110') - 1), '1'),
               CONCAT ('01110', '1'))
  -- If input string is  01110 output will be 01111
SELECT DECODE (SUBSTR ('01111', LENGTH ('01111')),
               0, CONCAT (SUBSTR ('01111', 1, LENGTH ('01111') - 1), '1'),
               CONCAT ('01111', '1'))
                 FROM DUAL
 -- If input string is  01111 output will be  011111

Replace 01110 or 01111 with the altid
PortletPaulEE Topic AdvisorCommented:
as awking00 has already stated, LPAD() should suit you
N+1    LPAD(TO_CHAR(N+1),8,'0')
2       00000002
11      00000011
101     00000101
1001    00001001
10001   00010001
100001  00100001
1000001 01000001

Open in new window

with CTE as (
              select 1 as n from dual union all
              select 10 as n from dual union all
              select 100 as n from dual union all
              select 1000 as n from dual union all
              select 10000 as n from dual union all
              select 100000 as n from dual union all
              select 1000000 as n from dual
, lpad(to_char(n+1), 8,'0')
from cte

Open in new window

Just adjust for the wanted width (2nd parameter)
Tanuja KadamProgrmmer AnalystAuthor Commented:
Here is my sql,

                        SELECT max(replace(altid, max(substr(altid,6,(length(altid)-8))),max(substr(altid,6,(length(altid)-8)))+1))
                        INTO new_billno1
                        FROM ALTIDINDX
                        WHERE taxyr = &&p_taxyr
                        AND parid like p_org_parid||'%'

I need to add 1 to the middle piece of altid. if the length of altid is 10 then starting from 6th position and ending at length-3. get the middle part and add 1 to id and replace the middle part.
The middle part may start with 0 but not always.
Abhimanyu SuriDatabase EngineerCommented:
Can you please explain with an example of altid

lets say -->  altid='ABCDE001IJ'

Now, assuming middle part is always going to be from position 6-8 i.e. 001 in this case.

Can you please explain what exactly you want to do, when you say get the middle part and add 1 to it ?
 Do you want it to be 0011 or leave it as 001 or something else ?
awking00Information Technology SpecialistCommented:
Can you provide some sample data of varied lengths, with and without 0s, and your expected results?
Tanuja KadamProgrmmer AnalystAuthor Commented:
Here is an example of altid,

altid = '2014003681100'

now the middle part is 003681. now I need to add 1 to it and not concat. so the middle part will become 003682. after replacing this part the new altid will be 2014003682100.
Abhimanyu SuriDatabase EngineerCommented:
select REPLACE ('2014003681100',substr('2014003681100',6,5),lpad(substr('2014003681100',6,5)+1,5,0)) from dual

 -- 2014003682100

select REPLACE ('2014013681100',substr('2014013681100',6,5),lpad(substr('2014013681100',6,5)+1,5,0)) from dual

 -- 2014013682100

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
I see what you are trying to accomplish, but I'm not sure we have enough information to provide a concrete answer. Are all altid's the same length? Are all "middles" the same length? What is the criteria for determining the "middle"?
awking00Information Technology SpecialistCommented:
Also, what is the data type of altid?
Tanuja KadamProgrmmer AnalystAuthor Commented:
No... the length of altid  or the middle part is not same always.

I think I got the answer thanks to Abhimanyu Suri

REPLACE (altid,max(substr(altid,6,(length(altid)-8))),lpad(max(substr(altid,6,(length(altid)-8)))+1,length(altid)-8,0))
Tanuja KadamProgrmmer AnalystAuthor Commented:
Thank you for your help.
Abhimanyu SuriDatabase EngineerCommented:
Welcome :)
slightwv (䄆 Netminder) Commented:
>>I think I got the answer thanks to Abhimanyu Suri

That has a pretty serious flaw.  What if the altid as the 5 extracted characters repeated somewhere before position 6?

This returns the wrong results since it altered the beginning part as well:
select REPLACE ('0368103681100', substr('0368103681100',6,5) ,lpad(substr('0368103681100',6,5)+1,5,0)) from dual;

Try this:
select substr('0368103681100',1,5) || lpad(substr('0368103681100',6,5)+1,5,0) || substr('0368103681100',11) from dual;
awking00Information Technology SpecialistCommented:
Maybe I'm dense, but still would like to know what determines middle.
slightwv (䄆 Netminder) Commented:
>>but still would like to know what determines middle.

Given this:  substr(altid,6,(length(altid)-8))

I have to assume from position 6 for length(altid)-8 characters.

Although, I see I copied the code from the accepted answer.  Looks like the wrong code was accepted?

Same issue though:  You can replace data you didn't mean to replace using the other code as well.
Abhimanyu SuriDatabase EngineerCommented:

Yes my suggestion has static values and idea behind is to not spoon feed but to actually work around a thought and explore options.
slightwv (䄆 Netminder) Commented:
I just wanted to point out that depending on the actual data, it has a potential flaw if used as-is.
Abhimanyu SuriDatabase EngineerCommented:
Thanks !! I'll also make sure that with next suggestions I do mention if it is an exact solution or simply a suggestion around the same line.
awking00Information Technology SpecialistCommented:
I'd still like to know how the "middle" is determined as I suspect there may be an easier solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.