Solved

Oracle to_char

Posted on 2016-08-04
22
87 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

Expert Comment

by:Sharath
ID: 41743280
Can you provide some more examples?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

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 49

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 5

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 5

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 5

Expert Comment

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

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 77

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 5

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 77

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 5

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

690 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