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
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
What are the advantages of informatica?
What are the challenges you have faced in your project?
Can anyone guide me the steps to install informatica PC 8.5.1 with database as oracle
What is olap (on-line analytical processing?
difference between stop and abort
What is Micro Strategy? Why is it used for?
Differentiate between a database and data warehouse?
wf dont have integration severances how you can run?
How you know when to use a static cache and dynamic cache in lookup transformation?
what is the incremental agrrigation?
SO many times i saw "$PM parser error " .what is meant by PM?
Describe two levels in which update strategy transformation sets?