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.