[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

Oracle to_char

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

How can I get the  answer as 01111
0
Tanuja Kadam
Asked:
Tanuja Kadam
  • 6
  • 6
  • 5
  • +3
1 Solution
 
awking00Commented:
SELECT LPAD(TO_CHAR(TO_NUMBER('01110') + 1),5,'0') FROM dual
1
 
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.
max(substr(altid,6,(length(altid)-8)))+1)
0
 
SharathData EngineerCommented:
Can you provide some more examples?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Abhimanyu SuriCommented:
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
0
 
PortletPaulCommented:
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
           )
select
  to_char(n+1)
, lpad(to_char(n+1), 8,'0')
from cte

Open in new window

Just adjust for the wanted width (2nd parameter)
0
 
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.
0
 
Abhimanyu SuriCommented:
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 ?
0
 
awking00Commented:
Can you provide some sample data of varied lengths, with and without 0s, and your expected results?
0
 
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.
0
 
Abhimanyu SuriCommented:
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
0
 
awking00Commented:
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"?
0
 
awking00Commented:
Also, what is the data type of altid?
0
 
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))
0
 
Tanuja KadamProgrmmer AnalystAuthor Commented:
Thank you for your help.
0
 
Abhimanyu SuriCommented:
Welcome :)
0
 
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;
0
 
awking00Commented:
Maybe I'm dense, but still would like to know what determines middle.
0
 
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.
0
 
Abhimanyu SuriCommented:
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.
0
 
slightwv (䄆 Netminder) Commented:
I just wanted to point out that depending on the actual data, it has a potential flaw if used as-is.
0
 
Abhimanyu SuriCommented:
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.
0
 
awking00Commented:
I'd still like to know how the "middle" is determined as I suspect there may be an easier solution.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 6
  • 6
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now