Format a number - TO_CHAR() 2

[Andrzejek]

My Company wants to represent simple numbers, like 123.56 as 1+23.56[pre]
0 would be 0+00.00
1 would be 0+01.00
2 would be 0+02.00
3 would be 0+03.00
3.5 would be 0+03.50
...
12034.56 would be 120+34.56[/pre]

I can keep this data as VARCHAR2, but I would rather keep it as a NUMBER(13, 2) and display it in this weird way.
If I do keep it as a NUMBER, how can I format that number to get the right display?
Something like:[tt]
SELECT TO_CHAR(1290, '99999999990[red]+[/red]00.00') AS MYNUMBER FROM DUAL[/tt]
but the [red]+[/red] causes an error...

Positive values only.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

It's ugly, but so is the requirement:

SUBSTR(TO_CHAR(mynum,'9999999999.00'),1,LENGTH(TO_CHAR(mynum,'9999999999.00'))-5)||'+'||SUBSTR(TO_CHAR(mynum,'9999999999.00'),(LENGTH(TO_CHAR(mynum,'9999999999.00'))-4,5)

I haven't tested this, but I think it should get you within striking distance.
Good luck - with requirements like this, you may need it!

 

[Andrzejek]

Thanks carp,
After a little modification it would be:
[tt]
SELECT
SUBSTR(TO_CHAR(mynum,'9999999[red]000.00[/red]'),1,LENGTH(TO_CHAR(mynum,'9999999[red]000.00[/red]'))-5)
||'+'||
SUBSTR(TO_CHAR(mynum,'9999999[red]000.00[/red]'),(LENGTH(TO_CHAR(mynum,'9999999[red]000.00[/red]'))-4)) AS Station
FROM DUAL
[/tt]
Just FYI, that's how the numbers along the roads are displayed, during the design and construction. it is kind of a 'ruler' along the road.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[carp]

Andy - I was thinking about numbers below 100 this morning and I was just going to come in and edit in those additional zeroes, but you saved me the effort. And thanks for the explanation of the requirement - I was hoping there was a good reason for it!

 

[Andrzejek]

In a perfect world, I would establish a Function in my data base to convert a number to a Station as text, but since what I work on now will only be used for a year or so, I will not bother.
If you want to know more about Stations (or cannot sleep ), you can read CHAPTER 6: STATIONING AND SYMBOLS here

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Yawn

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!