CODEWITHSUNDEEP
sqloracle-database
Sarica
Sarica

Reputation: 19

How can I write SQL SELECT statement to convert numbers into their words

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

Upvotes: 0

Views: 1531

Answers (2)

Y.B.
Y.B.

Reputation: 3586

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

Upvotes: 2

Jaseer
Jaseer

Reputation: 52

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;

Upvotes: 0

Related Questions