Oracle中不使用内置函数实现数字转英文单词的SQL方案咨询

Alright, I get it—you want to ditch the handy TO_CHAR(to_date(sal,'j'),'Jsp') trick and build a number-to-word converter from scratch using pure SQL, no built-in conversion functions allowed. Let's walk through how to pull this off.

Core Idea #

We'll manually map numeric values to their English word equivalents, then use basic math operations (division, modulus) to split the input number into its individual place values (ones, tens, hundreds, thousands, etc.), and finally stitch those words together correctly.

Working SQL Implementation #

Here's a self-contained query that handles numbers from 0 up to 999,999 (you can extend it further for larger values if needed):

WITH ones AS (
    SELECT 0 num, 'zero' word FROM dual UNION ALL
    SELECT 1, 'one' FROM dual UNION ALL
    SELECT 2, 'two' FROM dual UNION ALL
    SELECT 3, 'three' FROM dual UNION ALL
    SELECT 4, 'four' FROM dual UNION ALL
    SELECT 5, 'five' FROM dual UNION ALL
    SELECT 6, 'six' FROM dual UNION ALL
    SELECT 7, 'seven' FROM dual UNION ALL
    SELECT 8, 'eight' FROM dual UNION ALL
    SELECT 9, 'nine' FROM dual UNION ALL
    SELECT 10, 'ten' FROM dual UNION ALL
    SELECT 11, 'eleven' FROM dual UNION ALL
    SELECT 12, 'twelve' FROM dual UNION ALL
    SELECT 13, 'thirteen' FROM dual UNION ALL
    SELECT 14, 'fourteen' FROM dual UNION ALL
    SELECT 15, 'fifteen' FROM dual UNION ALL
    SELECT 16, 'sixteen' FROM dual UNION ALL
    SELECT 17, 'seventeen' FROM dual UNION ALL
    SELECT 18, 'eighteen' FROM dual UNION ALL
    SELECT 19, 'nineteen' FROM dual
),
tens AS (
    SELECT 2 num, 'twenty' word FROM dual UNION ALL
    SELECT 3, 'thirty' FROM dual UNION ALL
    SELECT 4, 'forty' FROM dual UNION ALL
    SELECT 5, 'fifty' FROM dual UNION ALL
    SELECT 6, 'sixty' FROM dual UNION ALL
    SELECT 7, 'seventy' FROM dual UNION ALL
    SELECT 8, 'eighty' FROM dual UNION ALL
    SELECT 9, 'ninety' FROM dual
),
number_parts AS (
    SELECT
        sal,
        -- Split into thousands, hundreds, tens/ones
        FLOOR(sal / 1000) AS thousand_part,
        MOD(FLOOR(sal / 100), 10) AS hundred_part,
        MOD(sal, 100) AS ten_one_part
    FROM emp
),
hundred_conversion AS (
    SELECT
        np.sal,
        np.thousand_part,
        -- Convert hundreds place
        CASE
            WHEN np.hundred_part = 0 THEN ''
            ELSE o1.word || ' hundred'
        END AS hundred_word,
        -- Convert tens/ones place
        CASE
            WHEN np.ten_one_part = 0 THEN ''
            WHEN np.ten_one_part < 20 THEN o2.word
            ELSE t.word || CASE WHEN MOD(np.ten_one_part,10) !=0 THEN ' ' || o3.word ELSE '' END
        END AS ten_one_word
    FROM number_parts np
    LEFT JOIN ones o1 ON np.hundred_part = o1.num
    LEFT JOIN ones o2 ON np.ten_one_part = o2.num
    LEFT JOIN tens t ON FLOOR(np.ten_one_part /10) = t.num
    LEFT JOIN ones o3 ON MOD(np.ten_one_part,10) = o3.num
)
SELECT
    sal,
    -- Combine all parts into final word string
    TRIM(
        CASE
            WHEN thousand_part = 0 THEN hundred_word || CASE WHEN hundred_word != '' AND ten_one_word != '' THEN ' ' ELSE '' END || ten_one_word
            ELSE (SELECT word FROM ones WHERE num = thousand_part) || ' thousand' || 
                 CASE WHEN hundred_word != '' THEN ' ' || hundred_word ELSE '' END ||
                 CASE WHEN ten_one_word != '' THEN ' ' || ten_one_word ELSE '' END
        END
    ) AS in_words
FROM hundred_conversion;

How This Works #

Let's break down the key pieces:

• ones CTE: Maps numbers 0-19 to their exact English words (since these don't follow a consistent tens+ones pattern).
• tens CTE: Maps 20,30,...,90 to their word equivalents.
• number_parts: Uses basic division and modulus to split each salary into thousands, hundreds, and the remaining tens/ones digits.
• hundred_conversion: Converts each individual place value to its word form, handling edge cases like zero values (so we don't end up with extra spaces or words).
• Final SELECT: Stitches all the converted parts together, trimming any extra spaces and handling numbers with no thousands/hundreds/tens parts.

Extending for Larger Numbers #

If you need to handle numbers bigger than 999,999, you can add more CTEs for ten-thousands, hundred-thousands, millions, etc.—just follow the same pattern: split the number into higher place values, map them to words, and add the appropriate scale term (like 'million') when stitching.

内容的提问来源于stack exchange，提问作者Enferno