0

Below is my table:

ENAME   SALARY
AAAA    $1254.20
GGGG    $4565
RRRR    $7889.60
WEFDSF  $0.00
XXXX    N/A

I want that salary to be display in words, for eg: 1254.20 should be displayed as one thousand two hundred fifty four dollars and twenty cents

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Sarica
  • 19
  • 4
  • 1
    Any attempt at it yourself that you could share? – An0nC0d3r Jan 10 '16 at 18:46
  • 6
    I don't think it's a job for an SQL query, better implement it in the representation layer. – bereal Jan 10 '16 at 18:47
  • 1
    Which DBMS are you using? –  Jan 10 '16 at 18:47
  • I tried with - SELECT ENAME,TO_CHAR((SALARY),'$9,999.99') SALARY, TO_CHAR (TO_DATE (1254, 'j'), 'jsp') AS TO_WORDS FROM T1 – Sarica Jan 10 '16 at 18:50
  • Try [How can I convert an integer into its verbal representation](http://stackoverflow.com/questions/554314/how-can-i-convert-an-integer-into-its-verbal-representation) you can get logic out of it. – tchelidze Jan 10 '16 at 19:37

2 Answers2

2

Apparently there is a hack for integers using conversion to julian date: HOW TO: Spell a number or convert a number to words

SELECT TO_CHAR (TO_DATE (1234567, 'j'), 'jsp') FROM DUAL;

You can split SALARY into integer dollars and cents and apply the hack:

Select
  TO_CHAR (TO_DATE (Dollars, 'j'), 'jsp') || ' dollars ' ||
  TO_CHAR (TO_DATE (Cents, 'j'), 'jsp') || ' cents' As Spelling
From (
  Select
    TRUNC(1254.20) As Dollars,
    (1254.20 - TRUNC(1254.20)) * 100 As Cents
  From DUAL);

Admittedly you also need to cover for single dollar / cent. Best be done in a function.

CREATE OR REPLACE FUNCTION spell_amount
  (srcAmnt IN NUMBER)
  RETURN VARCHAR2
IS
  Dollars INT;
  Cents INT;
  Result VARCHAR2(1000);
BEGIN
  Dollars := Trunc(srcAmnt);
  Cents := Trunc((srcAmnt - Dollars)*100);

  IF Dollars = 0 THEN
    Result := 'no dollars';
  ELSIF Dollars = 1 THEN
    Result := 'one dollar';
  ELSE
    Result := TO_CHAR (TO_DATE (Dollars, 'j'), 'jsp') || ' dollars';
  END IF;

  IF Cents = 0 THEN
    Result := Result || ' no cents';
  ELSIF Cents = 1 THEN
    Result := Result || ' one cent';
  ELSE
    Result := Result || ' ' || TO_CHAR (TO_DATE (Cents, 'j'), 'jsp') || ' cents';
  END IF;

  RETURN Result;
END spell_amount;
/

And then

Select spell_amount(132.01) From DUAL;

Some more on ORA-01854: julian date must be between 1 and 5373484 limitation of Julian date and possible solution: How To Convert Number into Words using Oracle SQL Query

And SQL Fiddle

Y.B.
  • 3,526
  • 14
  • 24
0

Substitute 12345.15 with your column name and remove $ sign from the column before substitution.

select TO_CHAR(TO_DATE(floor(12345.15),'J'),'JSP')||' Dollars '|| case when 12345.15 - FLOOR(12345.15)>0 then 'And '||TO_CHAR(TO_DATE(substr('12345.15',instr('12345.15','.')+1),'J'),'JSP') || ' Cents' end INWORDS from dual;

Jaseer
  • 52
  • 9