求通用PL/SQL代码转换特殊字符为ASCII，解决转义后变问号问题

I’ve run into this exact question mark issue before—it almost always comes down to not properly decoding HTML entities first or mismatched character sets in your Oracle database. Let’s break down the fix step by step:

1. Why You’re Seeing Question Marks #

• You’re probably trying to convert the raw HTML entity string (like Ü) directly instead of first turning it into the actual Unicode character (Ü).
• Your database’s character set might not support Unicode (e.g., using US7ASCII instead of AL32UTF8), so it can’t render the special character and replaces it with ?.

2. Step-by-Step PL/SQL Solution #

We’ll create a two-part function: first decode HTML numeric entities to their Unicode equivalents, then transliterate those special characters to their standard ASCII counterparts.

First: Decode HTML Numeric Entities #

This function parses strings like Ü and converts them to the corresponding Unicode character:

CREATE OR REPLACE FUNCTION decode_html_numeric_entity(p_input IN VARCHAR2)
RETURN VARCHAR2
IS
    v_result VARCHAR2(4000) := p_input;
    v_start  PLS_INTEGER;
    v_end    PLS_INTEGER;
    v_code   PLS_INTEGER;
BEGIN
    LOOP
        -- Find the start of a numeric entity (&#...)
        v_start := INSTR(v_result, '&#');
        EXIT WHEN v_start = 0;
        
        -- Find the end of the entity (;)
        v_end := INSTR(v_result, ';', v_start);
        EXIT WHEN v_end = 0;
        
        -- Extract the numeric code and convert to character
        v_code := TO_NUMBER(SUBSTR(v_result, v_start + 2, v_end - v_start - 2));
        v_result := REPLACE(v_result, SUBSTR(v_result, v_start, v_end - v_start + 1), 
                            CHR(v_code USING NCHAR_CS)); -- Use national character set for Unicode
    END LOOP;
    RETURN v_result;
END;
/

Second: Transliterate Special Characters to ASCII #

Use Oracle’s built-in UTL_I18N.TRANSLITERATE function to map special Unicode characters to their ASCII equivalents (e.g., Ü → U, Ó → O). We’ll wrap this with the entity decoder:

CREATE OR REPLACE FUNCTION convert_to_ascii(p_input IN VARCHAR2)
RETURN VARCHAR2
IS
    v_decoded VARCHAR2(4000);
    v_ascii   VARCHAR2(4000);
BEGIN
    -- Step 1: Decode HTML numeric entities
    v_decoded := decode_html_numeric_entity(p_input);
    
    -- Step 2: Transliterate to ASCII (use ICU rule for broad compatibility)
    v_ascii := UTL_I18N.TRANSLITERATE(v_decoded, 'Any-Latin; Latin-ASCII', 'AL32UTF8');
    
    -- Optional: Remove any remaining non-ASCII characters (if needed)
    v_ascii := REGEXP_REPLACE(v_ascii, '[^[:ascii:]]', '');
    
    RETURN v_ascii;
END;
/

3. Test the Function #

Let’s run your examples to verify:

-- Test 1: ÜNLÜ → UNLU
SELECT convert_to_ascii('ÜNLÜ') FROM DUAL;

-- Test 2: JÓNÁS → JONAS
SELECT convert_to_ascii('JÓNÁS') FROM DUAL;

4. Critical Notes #

• Database Character Set: Ensure your database uses AL32UTF8 (Unicode) as the default character set. If it’s using an older set like US7ASCII, the CHR function won’t generate correct Unicode characters.
• Oracle Version: The Any-Latin; Latin-ASCII rule works in Oracle 12c and later. For older versions, use a locale-specific rule like GERMAN_DIN (for German umlauts) or SPANISH (for Spanish accents).
• Named Entities: If you need to handle named entities like ü, extend the decoder function to map those to their Unicode equivalents (you’ll need a lookup table for common entities).

内容的提问来源于stack exchange，提问作者Md.Shafi Uzzama