Solved

Oracle to_char

Posted on 2016-08-04
22
82 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 32

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

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 32

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
 
LVL 32

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 32

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 32

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 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