## Question and Answer

## You Asked

I want output as:

numbers string

101 One zero one

102 One zero two

851 eight five one

9856 nine eight five six

356 three five six

748 seven four eight

254 two five four

How can i get string in this way.

numbers string

101 One zero one

102 One zero two

851 eight five one

9856 nine eight five six

356 three five six

748 seven four eight

254 two five four

How can i get string in this way.

## and Connor said...

You might see some examples using the JSP format to spell a number, eg

but that wont work here, because zero is not allowed

So we'll just use a simple CASE statement, and "connect by level" to cycle through each character

So now we just need to incorporate that into our table of values. Here's how we can get each element within each row of a table

so now we just incorporate our case statement and we're nearly there

Now we need to aggregate those strings - on 11g upwards you can use LISTAGG but since you're on 10g you'll need to roll your own. See the examples in this question for details.

https://asktom.oracle.com/pls/apex/asktom.search?tag=stragg#16551777586484

Then it will just be

select x, stragg(str)

from

( ... the above )

group by x

SQL> select to_char(to_date('7','J'),'JSP') from dual; TO_CH ----- SEVEN

but that wont work here, because zero is not allowed

SQL> select to_char(to_date('0','J'),'JSP') from dual; select to_char(to_date('0','J'),'JSP') from dual * ERROR at line 1: ORA-01854: julian date must be between 1 and 5373484

So we'll just use a simple CASE statement, and "connect by level" to cycle through each character

SQL> select 2 case x 3 when '0' then 'zero' 4 when '1' then 'one' 5 when '2' then 'two' 6 when '3' then 'three' 7 when '4' then 'four' 8 when '5' then 'five' 9 when '6' then 'six' 10 when '7' then 'seven' 11 when '8' then 'eight' 12 when '9' then 'nine' 13 end 14 from ( select '3' x from dual ) ; CASEX ----- three SQL> SQL> select 2 case x 3 when '0' then 'zero' 4 when '1' then 'one' 5 when '2' then 'two' 6 when '3' then 'three' 7 when '4' then 'four' 8 when '5' then 'five' 9 when '6' then 'six' 10 when '7' then 'seven' 11 when '8' then 'eight' 12 when '9' then 'nine' 13 end 14 from ( 15 select substr('123',rownum,1) x 16 from dual 17 connect by level <= 3 18 ) ; CASEX ----- one two three

So now we just need to incorporate that into our table of values. Here's how we can get each element within each row of a table

SQL> create or replace type string_list is table of varchar2(20); 2 / Type created. SQL> SQL> create table t ( x int ); Table created. SQL> SQL> insert into t values (101); 1 row created. SQL> insert into t values (456); 1 row created. SQL> insert into t values (789); 1 row created. SQL> SQL> select * 2 from t, 3 table(cast(multiset( 4 select substr(to_char(t.x),rownum,1) 5 from dual 6 connect by level <= length(to_char(t.x))) as string_list) 7 ); X COLUMN_VAL ---------- ---------- 101 1 101 0 101 1 456 4 456 5 456 6 789 7 789 8 789 9 9 rows selected.

so now we just incorporate our case statement and we're nearly there

SQL> select 2 x, 3 case digit 4 when '0' then 'zero' 5 when '1' then 'one' 6 when '2' then 'two' 7 when '3' then 'three' 8 when '4' then 'four' 9 when '5' then 'five' 10 when '6' then 'six' 11 when '7' then 'seven' 12 when '8' then 'eight' 13 when '9' then 'nine' 14 end str 15 from ( 16 select x, column_value digit 17 from t, 18 table(cast(multiset( 19 select substr(to_char(t.x),rownum,1) 20 from dual 21 connect by level <= length(to_char(t.x))) as string_list) 22 ) 23 ) 24 SQL> SQL> SQL> / X STR ---------- ----- 101 one 101 zero 101 one 456 four 456 five 456 six 789 seven 789 eight 789 nine 9 rows selected.

Now we need to aggregate those strings - on 11g upwards you can use LISTAGG but since you're on 10g you'll need to roll your own. See the examples in this question for details.

https://asktom.oracle.com/pls/apex/asktom.search?tag=stragg#16551777586484

Then it will just be

select x, stragg(str)

from

( ... the above )

group by x

## Rating

Is this answer out of date? If it is, please let us know via a Comment

# Comments

Hiya,

Just for fun: an alternative without using string aggregation:

Since we only need to handle each digit separately (and not write "forty-two hundred and sixty-nine"), replacing each "string digit" with it's "word equivalent" will do. SUBSTR just gets rid of the leading space.

Whether it is more performant to do a series of REPLACE compared to creating rows per digit and aggregating back, that is a question I have not tested ;-)

Cheerio

/Kim

Just for fun: an alternative without using string aggregation:

with n as ( select 101 num from dual union all select 102 num from dual union all select 851 num from dual union all select 9856 num from dual union all select 356 num from dual union all select 748 num from dual union all select 254 num from dual ) select n.num , substr( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( to_char(n.num,'TM9') , '0', ' zero' ) , '1', ' one' ) , '2', ' two' ) , '3', ' three') , '4', ' four' ) , '5', ' five' ) , '6', ' six' ) , '7', ' seven') , '8', ' eight') , '9', ' nine' ) , 2 ) as string from n ;

Since we only need to handle each digit separately (and not write "forty-two hundred and sixty-nine"), replacing each "string digit" with it's "word equivalent" will do. SUBSTR just gets rid of the leading space.

Whether it is more performant to do a series of REPLACE compared to creating rows per digit and aggregating back, that is a question I have not tested ;-)

Cheerio

/Kim

nice work