i have source data like id name sal 1 a 65020 2 b 78250 3 c 58264

i have source data like

id name sal
1 a 65020
2 b 78250
3 c 58264

but how to get target like this

id name sal
1 a Sixty five thousand twenty
2 b Seventy thousand two fify
3 c Fifty eight thousand two sixty four

Question Posted / vishal.moluguri

3 Answers
7218 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

i have source data like id name sal 1 a 65020 2 b 78250 3 c 58264 but how to get t..

Answer / rahul singh

We can use a simple function (TO_CHAR(TO_DATE(1234567,'J'),
'JSP')) this will return you ONE MILLION TWO HUNDRED
THIRTY-FOUR THOUSAND FIVE HUNDRED SIXTY-SEVEN . But this has
limitation to be used only for numbers between 1 and 5373484
.For numbers larger than that use following function and
call it in informatica through external function
transformation .

create or replace
2 function spell_number( p_number in number )
3 return varchar2
4 as
5 type myArray is table of varchar2(255);
6 l_str myArray := myArray( '',
7 ' thousand ', ' million ',
8 ' billion ', ' trillion ',
9 ' quadrillion ', '
quintillion ',
10 ' sextillion ', ' septillion ',
11 ' octillion ', ' nonillion ',
12 ' decillion ', ' undecillion ',
13 ' duodecillion ' );
14
15 l_num varchar2(50) default trunc( p_number );
16 l_return varchar2(4000);
17 begin
18 for i in 1 .. l_str.count
19 loop
20 exit when l_num is null;
21
22 if ( substr(l_num, length(l_num)-2, 3) <> 0 )
23 then
24 l_return := to_char(
25 to_date(
26 substr(l_num,
length(l_num)-2, 3),
27 'J' ),
28 'Jsp' ) || l_str(i) || l_return;
29 end if;
30 l_num := substr( l_num, 1, length(l_num)-3 );
31 end loop;
32
33 return l_return;
34 end;

Is This Answer Correct ?

4 Yes

0 No

i have source data like id name sal 1 a 65020 2 b 78250 3 c 58264 but how to get t..

Answer / chiky

if its in oracle database,use

select id, name, (SELECT TO_CHAR (TO_DATE sal, 'j'), 'jsp')
FROM DUAL) sal from <source_table_name>

Is This Answer Correct ?

0 Yes

0 No

i have source data like id name sal 1 a 65020 2 b 78250 3 c 58264 but how to get t..

Answer / krish

decode(sal,65020,Sixty five thousand twenty)
decode(sal,78250,Seventy thousand two fify)
decode(sal,58264,Fifty eight thousand two sixty four)

Is This Answer Correct ?

3 Yes

10 No

Post New Answer