Home » SQL & PL/SQL » SQL & PL/SQL » number to char conversion
number to char conversion [message #209052] Wed, 13 December 2006 01:56 Go to next message
brihaspatirai
Messages: 24
Registered: November 2006
Location: pune
Junior Member
i want to convert a number into spell form like 100 as a one zero zero
Re: number to char conversion [message #209054 is a reply to message #209052] Wed, 13 December 2006 02:01 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

check it attach file
hope this helps.
Taj
Re: number to char conversion [message #209056 is a reply to message #209052] Wed, 13 December 2006 02:16 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The script
var thenumber NUMBER
exec :thenumber := 100;

col thestring format a30

SELECT MAX(TRIM(sys_connect_by_path(digit,' '))) thestring
FROM ( SELECT decode(substr(to_char(:thenumber),level,1)
                    , '0', 'zero'
                    , '1', 'one'
                    , '2', 'two'
                    , '3', 'three'
                    , '4', 'four'
                    , '5', 'five'
                    , '6', 'six'
                    , '7', 'seven'
                    , '8', 'eight'
                    , '9', 'nine'
                    ) digit
           , level l
           , :thenumber thenumber
       FROM   dual
       CONNECT BY level <= length(to_char(:thenumber))
     )
CONNECT BY prior l < l
START WITH l = 1
/

exec :thenumber := 1234;

/

The run:
SQL> @orafaq

PL/SQL procedure successfully completed.


THESTRING
------------------------------
one zero zero


PL/SQL procedure successfully completed.


THESTRING
------------------------------
one two three four

SQL>


Option two (using stragg or concat_all - search the board or asktom.oracle.com for it).

The script:
var thenumber NUMBER
exec :thenumber := 100;

col thestring format a30

SELECT concat_all(concat_expr(digit,' ')) thestring
FROM ( SELECT decode(substr(to_char(:thenumber),level,1)
                    , '0', 'zero'
                    , '1', 'one'
                    , '2', 'two'
                    , '3', 'three'
                    , '4', 'four'
                    , '5', 'five'
                    , '6', 'six'
                    , '7', 'seven'
                    , '8', 'eight'
                    , '9', 'nine'
                    ) digit
           , level l
           , :thenumber thenumber
       FROM   dual
       CONNECT BY level <= length(to_char(:thenumber))
     )
/

exec :thenumber := 1234;

/


MHE

[Updated on: Wed, 13 December 2006 02:16]

Report message to a moderator

Previous Topic: rows as columns in sql
Next Topic: how to change unused to used columns
Goto Forum:
  


Current Time: Sun Dec 11 04:16:11 CST 2016

Total time taken to generate the page: 0.10238 seconds