?
Solved

Conversion of a number field in oracle sql

Posted on 2012-04-02
5
Medium Priority
?
392 Views
Last Modified: 2012-04-02
Hi,

I am having a field which is a number(4) and it has values like
5000,-6000,-44,-56

I want it to be converted to
50:00
-60:00
-44:00
-56:00

Can this be done.

Please let me know.

Regards..
0
Comment
Question by:neoarwin
  • 3
5 Comments
 
LVL 8

Accepted Solution

by:
Christoffer Swanström earned 2000 total points
ID: 37795571
What exactly would the logic for the conversion be? Take the first two digits, then add a colon and after the colon all the remaining digits (fill with zeros)?

For the logic above, do something like this:

SELECT
      nbr
      ,CASE WHEN nbr < 0 THEN '-' ELSE '' END || SUBSTR(TO_CHAR(ABS(nbr)), 1, 2) || ':' || NVL(SUBSTR(TO_CHAR(ABS(nbr)), 3, 1), 0) || NVL(SUBSTR(TO_CHAR(ABS(nbr)), 4, 1), 0) AS nbr2
FROM
(SELECT 50 AS nbr FROM dual

UNION ALL

SELECT -6000 AS nbr FROM dual

UNION ALL

SELECT -44 AS nbr FROM dual

UNION ALL

SELECT -56 AS nbr FROM dual
) asd
0
 
LVL 10

Expert Comment

by:Bawer
ID: 37795593
select to_char(column,'999,999,999,990.00') from table
0
 

Author Comment

by:neoarwin
ID: 37795667
@ bawer this won't work out.
as it will give -6000:00 , 5000:00
I need -60:00 for 6000 and 50:00 for 5000

@tosse I have many values in that table, i gave 4 values as an example..
how to change your query in that case?
0
 

Author Comment

by:neoarwin
ID: 37795679
@ tosse I found the way from your query, thanks.
0
 

Author Closing Comment

by:neoarwin
ID: 37795683
Thank you :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

616 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