
How to spell a numeric value in english words
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
Thanks Dieter for this useful function :-)
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
Regards
Subbu
Hi Subbu,
please open a new topic for new questions instead of commenting on a totally unrelated blog post.
Dieter
Dieter
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
Regards
Subbu
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
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
Dieter