How to spell a numeric value in english words

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Junior Contributor

In a recent topic in the forums there was a question on "how to spell out numeric values in english" and an excerpt from the manuals indicating there is a format for the new TO_CHAR function in TD14: "Any numeric element followed by SP is spelled in English words."

The manuals are a bit misleading, they don't mention that this option is only available for TO_CHAR(DateTime). It's based on Oracle's implementation and indeed embarrassing as year or julian day  are also numeric values, but you can't pass a numeric value, it must be extracted from a DATE.

 

Teradata's date range is less than Oracle's, so the well-known "julian day" trick TO_CHAR(TO_DATE(numericval,'j'), 'jsp') doesn't work as Oracle's calendar starts on January 1, 4712 BCE, thus the lowest date in Teradata is julian day 1721426 in Oracle:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT TO_CHAR(DATE '0001-01-01', 'jsp') (VARCHAR(100));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

TO_CHAR(0001-01-01,'jsp')
---------------------------------------------------------------------
one million seven hundred twenty-one thousand four hundred twenty-six

But based on this knowledge you can apply some modifications to get up to 6 digits:

SEL 123456 AS x,
CASE
WHEN x = 0 THEN 'zero'
ELSE SUBSTRING((TO_CHAR(DATE '0763-09-18' + ABS(x), 'jsp') (VARCHAR(100))) FROM 13)
END;

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

x <CASE expression>
----------- ---------------------------------------------------------------
123456 one hundred twenty-three thousand four hundred fifty-six

Fiinally add some more calculations to cover the full range of a BIGINT and implement it as a SQL UDF:

REPLACE FUNCTION SpellNumeric (x BIGINT)
RETURNS VARCHAR(220)
LANGUAGE SQL
CONTAINS SQL
NOT DETERMINISTIC
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
CASE WHEN ABS(x) >= (1e+15 (BIGINT)) AND (ABS(x) / (1e+15 (BIGINT)) MOD 1000) <> 0 THEN SUBSTRING(TO_CHAR(DATE '0763-09-18' + ABS(x / (1e+15 (BIGINT)) MOD 1000), 'jsp') FROM 13) ||' quadrillion ' ELSE '' END
|| CASE WHEN ABS(x) >= (1e+12 (BIGINT)) AND (ABS(x) / (1e+12 (BIGINT)) MOD 1000) <> 0 THEN SUBSTRING(TO_CHAR(DATE '0763-09-18' + ABS(x / (1e+12 (BIGINT)) MOD 1000), 'jsp') FROM 13) ||' trillion ' ELSE '' END
|| CASE WHEN ABS(x) >= (1e+09 (BIGINT)) AND (ABS(x) / (1e+09 (BIGINT)) MOD 1000) <> 0 THEN SUBSTRING(TO_CHAR(DATE '0763-09-18' + ABS(x / (1e+09 (BIGINT)) MOD 1000), 'jsp') FROM 13) ||' billion ' ELSE '' END
|| CASE WHEN ABS(x) >= (1e+06 (BIGINT)) AND (ABS(x) / (1e+06 (BIGINT)) MOD 1000) <> 0 THEN SUBSTRING(TO_CHAR(DATE '0763-09-18' + ABS(x / (1e+06 (BIGINT)) MOD 1000), 'jsp') FROM 13) ||' million ' ELSE '' END
|| CASE WHEN x = 0 THEN 'zero' ELSE SUBSTRING((TO_CHAR(DATE '0763-09-18' + ABS(x) MOD (1000000 (BIGINT)), 'jsp') (VARCHAR(100))) FROM 13) END
;

SELECT 123456789012345678 (BIGINT) AS x, spellnumeric(x);

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

x spellnumeric(x)
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123456789012345678 one hundred twenty-three quadrillion four hundred fifty-six trillion seven hundred eighty-nine billion twelve million three hundred forty-five thousand six hundred seventy-eight 

Some remarks:

  • Sadly, there's no easy way to change the output to another language.
  • This function doesn't cover negative values (simply add a CASE WHEN x < 0 THEN 'minus ' ELSE '' END)
  • It's based on american english, but could be easily modified to the european numbering scheme (billion = milliard, etc.).  
  • And of course it's not tested for the full range of values smiley
7 Comments
Enthusiast

Thanks Dieter for this useful function :-)

Enthusiast

Hi ,

My table have the data in the below format.We have to update COL3 value of 1st row with 2nd row value (i.e 2000 should be updated with 3000 simlarly 3000 should be updated with 4000 and the last value should be null).

Can you please  help me to write the query to achive the above result.

COL1 COL2 COL3

1 100 2,000

2 100 3,000

3 100 4,000

4 300 5,000

5 300 6,000

6 300 7,000

Thanks

Subbu

Junior Contributor

Hi Subbu,

please open a new topic for new questions instead of commenting on a totally unrelated blog post.

update tab as t
set col3 = (select col3 from tab as t2 where t2.col1 = t.col1+1)

Dieter

Enthusiast

Hi Dieter,

Thanks your help.I'm new to this forum,joined y'day.So I have posted my query on unrelated blog.

Today My account has been approved hence I will post any questions on my blog.

Thank You once again.

Regards

Subbu

Enthusiast

Super Amazing fun sql activity :)

Hi Dnoeth,

Coudl you help me,how to handle the Temporal in teradata, we are facing the issue for handling type 2 ,using this  Temporal txn_ts.

My source table contain duplicates and i want to load the duplicats also, duplicates in the sence not exact but date field has some nano second difference. if i try to update the active record, the duplicates or inactive records are also getting update, please help on this senerio as earliest.

Thanks,

Abi

Junior Contributor

Hi Abi,

could you please post totally unrelated questions as a new topic in the forums?

I don't have any expertice with sub-second temporal data, you should ask your Teradata support.

Dieter