基于PL/SQL length函数实现数字转英文单词的优化问题

Great start with your num_to_words function! The issue right now is that you're only grabbing the first 3 characters of the spelled-out number when handling 3-digit values, which cuts off the trailing part for numbers like 101. Let's fix this with two approaches—one using Oracle's built-in functionality (super simple) and another that builds on your original LENGTH-based logic for learning purposes.

Approach 1: Use Oracle's Built-in JSP Format (Quick & Easy) #

Oracle has a handy format specifier JSP that converts Julian date numbers directly to spelled-out English words. This handles all edge cases (like 101, 123, 999) automatically without manual string manipulation. Here's how to rewrite your function:

CREATE OR REPLACE FUNCTION num_to_words (NUM IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  -- Handle 0 as a special case if needed
  IF NUM = 0 THEN
    RETURN 'ZERO';
  END IF;
  -- JSP converts the number to fully spelled words
  RETURN TO_CHAR(TO_DATE(NUM, 'J'), 'JSP');
END;

Testing This: #

• SELECT num_to_words(100) FROM DUAL; → Returns ONE HUNDRED
• SELECT num_to_words(101) FROM DUAL; → Returns ONE HUNDRED ONE
• SELECT num_to_words(123) FROM DUAL; → Returns ONE HUNDRED TWENTY THREE

Approach 2: Build on Your Original LENGTH-Based Logic #

If you want to manually handle the number breakdown using LENGTH (for learning), we'll split the 3-digit number into hundreds, tens, and units, then combine them with the correct words. First, create a helper function to handle numbers 1-99, then use it in your main function:

Step 1: Helper Function for 1-99 #

CREATE OR REPLACE FUNCTION num_1_to_99 (n IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  RETURN CASE
    WHEN n = 1 THEN 'ONE'
    WHEN n = 2 THEN 'TWO'
    WHEN n = 3 THEN 'THREE'
    WHEN n = 4 THEN 'FOUR'
    WHEN n = 5 THEN 'FIVE'
    WHEN n = 6 THEN 'SIX'
    WHEN n = 7 THEN 'SEVEN'
    WHEN n = 8 THEN 'EIGHT'
    WHEN n = 9 THEN 'NINE'
    WHEN n = 10 THEN 'TEN'
    WHEN n = 11 THEN 'ELEVEN'
    WHEN n = 12 THEN 'TWELVE'
    WHEN n = 13 THEN 'THIRTEEN'
    WHEN n = 14 THEN 'FOURTEEN'
    WHEN n = 15 THEN 'FIFTEEN'
    WHEN n = 16 THEN 'SIXTEEN'
    WHEN n = 17 THEN 'SEVENTEEN'
    WHEN n = 18 THEN 'EIGHTEEN'
    WHEN n = 19 THEN 'NINETEEN'
    WHEN n BETWEEN 20 AND 29 THEN 'TWENTY ' || num_1_to_99(MOD(n, 10))
    WHEN n BETWEEN 30 AND 39 THEN 'THIRTY ' || num_1_to_99(MOD(n, 10))
    WHEN n BETWEEN 40 AND 49 THEN 'FORTY ' || num_1_to_99(MOD(n, 10))
    WHEN n BETWEEN 50 AND 59 THEN 'FIFTY ' || num_1_to_99(MOD(n, 10))
    WHEN n BETWEEN 60 AND 69 THEN 'SIXTY ' || num_1_to_99(MOD(n, 10))
    WHEN n BETWEEN 70 AND 79 THEN 'SEVENTY ' || num_1_to_99(MOD(n, 10))
    WHEN n BETWEEN 80 AND 89 THEN 'EIGHTY ' || num_1_to_99(MOD(n, 10))
    WHEN n BETWEEN 90 AND 99 THEN 'NINETY ' || num_1_to_99(MOD(n, 10))
    ELSE ''
  END;
END;

Step 2: Updated Main Function Using LENGTH #

CREATE OR REPLACE FUNCTION num_to_words (NUM IN NUMBER) RETURN VARCHAR2 IS
  v_length NUMBER;
  v_words VARCHAR2(100);
  v_hundreds NUMBER;
  v_last_two NUMBER;
BEGIN
  v_length := LENGTH(TO_CHAR(NUM));
  v_hundreds := TRUNC(NUM / 100);
  v_last_two := MOD(NUM, 100);
  
  CASE v_length
    WHEN 1 THEN
      v_words := num_1_to_99(NUM);
    WHEN 2 THEN
      v_words := num_1_to_99(NUM);
    WHEN 3 THEN
      v_words := num_1_to_99(v_hundreds) || ' HUNDRED';
      -- Add the last two digits only if they're not 0
      IF v_last_two != 0 THEN
        v_words := v_words || ' ' || num_1_to_99(v_last_two);
      END IF;
    ELSE
      v_words := 'Numbers larger than 999 not supported';
  END CASE;
  
  -- Trim any trailing spaces from cases like 20 → TWENTY (no extra space)
  RETURN TRIM(v_words);
END;

Testing This: #

• SELECT num_to_words(100) FROM DUAL; → ONE HUNDRED
• SELECT num_to_words(101) FROM DUAL; → ONE HUNDRED ONE
• SELECT num_to_words(99) FROM DUAL; → NINETY NINE

The key fix here is that instead of truncating the spelled-out string, we're explicitly checking if the last two digits are non-zero and appending their word equivalent.

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