Dear all,

I have a problem with sql function:

I want to change Amount column to Words,
senirio is like this: i have a 2 column, 1 has name and 2 has amount in numbers
and i want to convert that amount from numbers to words:

eg 1000 to be "one thousand"
2000 the same to first one.

my sql statement:
SELECT DISTINCT A.CUSTOMER_NUMBER,A.CUSTOMER_NAME,

A.PARTY_TYPE"CONTRIBUTOR TYPE",A.CUSTOMER_CLASS_CODE,A.ATTRIBUTE1"CERTIFICATE NUMBER",

A.ATTRIBUTE2"CERTIFICATE AWARD NUMBER",A.ATTRIBUTE4"CERTIFICATE TYPE",A.ATTRIBUTE6"DATE ISSUED",B.AMOUNT ------i want this to be in words

FROM AR_CUSTOMERS_V A,RA_CUST_TRX_LINE_GL_DIST_ALL B,RA_CUSTOMER_TRX_ALL C

WHERE A.CUSTOMER_ID=C.BILL_TO_CUSTOMER_ID

AND C.CUSTOMER_TRX_ID=B.CUSTOMER_TRX_ID

Can any help me i will appreciate your contributions

Thanks

Malinda Ramadhanni
TEA

To do this with SQL would likely require that the flavor of SQL you are using (MySql, Oracle, Informix, DB2, etc) contain a function that will convert a number into word format. I've never heard of a function like this, tho maybe one exists.

I would think that a better way would be to find or write a function that will work on the data returned from the database.

For example, if you are using C/C++ for your program, you'd find or write a C/C++ function to convert the number to words. So as you process the rows returned from the SQL, you'd call this function each time you processed a row.

With this approach, the function would then also be available in situations that don't involve SQL data.

I imagine that you can find code for converting numbers to words, depending on what language you need.

http://www.sqlsnippets.com/en/topic...

set linesize 100
set recsep off

column numbers format a12
column words format a80 word_wrapped

set null '(null)'

select
to_char(i,'999,999,999') numbers ,
to_char( to_timestamp( lpad(i,9,'0'), 'FF9' ), 'FFSP' ) as words
from n2w
where i between 0 and 9999999
order by i ;
Hi there.