Oracle to_char

SELECT LPAD(TO_CHAR(TO_NUMBER('01110') + 1),5,'0') FROM dual

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.
max(substr(altid,6,(length(altid)-8)))+1)

Can you provide some more examples?

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'))
  FROM DUAL
 
  -- 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

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

Select allOpen 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
           )
select
  to_char(n+1)
, lpad(to_char(n+1), 8,'0')
from cte

Select allOpen in new window

Just adjust for the wanted width (2nd parameter)

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.

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 ?

Can you provide some sample data of varied lengths, with and without 0s, and your expected results?

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.

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"?

Also, what is the data type of altid?

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))

Thank you for your help.

Welcome :)

>>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;

Maybe I'm dense, but still would like to know what determines middle.

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

Experts,

Yes my suggestion has static values and idea behind is to not spoon feed but to actually work around a thought and explore options.

I just wanted to point out that depending on the actual data, it has a potential flaw if used as-is.

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.

I'd still like to know how the "middle" is determined as I suspect there may be an easier solution.