Solved

Oracle to_char

Posted on 2016-08-04
22
74 Views
Last Modified: 2016-11-15
In oracle
SELECT TO_CHAR('01110' + 1) FROM dual;

How can I get the  answer as 01111
0
Comment
Question by:Tanuja Kadam
  • 6
  • 6
  • 5
  • +3
22 Comments
 
LVL 31

Expert Comment

by:awking00
ID: 41743112
SELECT LPAD(TO_CHAR(TO_NUMBER('01110') + 1),5,'0') FROM dual
1
 

Author Comment

by:Tanuja Kadam
ID: 41743121
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
 
LVL 40

Expert Comment

by:Sharath
ID: 41743280
Can you provide some more examples?
0
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41743289
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41744021
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
 

Author Comment

by:Tanuja Kadam
ID: 41744151
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
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41744165
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
 
LVL 31

Expert Comment

by:awking00
ID: 41744166
Can you provide some sample data of varied lengths, with and without 0s, and your expected results?
0
 

Author Comment

by:Tanuja Kadam
ID: 41744205
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
 
LVL 4

Accepted Solution

by:
Abhimanyu Suri earned 500 total points
ID: 41744227
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
 
LVL 31

Expert Comment

by:awking00
ID: 41744326
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Expert Comment

by:awking00
ID: 41744329
Also, what is the data type of altid?
0
 

Author Comment

by:Tanuja Kadam
ID: 41744355
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
 

Author Closing Comment

by:Tanuja Kadam
ID: 41744359
Thank you for your help.
0
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41744392
Welcome :)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41744742
>>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
 
LVL 31

Expert Comment

by:awking00
ID: 41744786
Maybe I'm dense, but still would like to know what determines middle.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41744798
>>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
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41744832
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41744838
I just wanted to point out that depending on the actual data, it has a potential flaw if used as-is.
0
 
LVL 4

Expert Comment

by:Abhimanyu Suri
ID: 41744849
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
 
LVL 31

Expert Comment

by:awking00
ID: 41888048
I'd still like to know how the "middle" is determined as I suspect there may be an easier solution.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now