Oracle 11G中匹配字符串内独立零的正则表达式需求

Got it, let's work through how to detect standalone zeros in a comma-separated string for your Oracle 11g stored procedure. The key is to ensure we only match 0 as a separate item—not as part of numbers like 10, 200, etc.

The Regular Expression Pattern #

We can use this concise regex pattern with Oracle's REGEXP_LIKE function:

'(^|,)0(,|$)'

Breakdown of the Pattern #

• (^|,): Matches either the start of the string (^) or a comma (,)—this ensures the 0 isn't preceded by another digit.
• 0: The literal zero we're targeting.
• (,|$): Matches either a comma (,) or the end of the string ($)—this ensures the 0 isn't followed by another digit.

Usage in a Stored Procedure #

Here's an example of how to integrate this into an Oracle stored procedure:

CREATE OR REPLACE PROCEDURE process_input_string(p_input VARCHAR2)
IS
BEGIN
    IF REGEXP_LIKE(p_input, '(^|,)0(,|$)') THEN
        -- Your business logic when a standalone zero exists goes here
        DBMS_OUTPUT.PUT_LINE('Standalone zero detected! Executing related logic...');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No standalone zero found. Proceeding with other logic...');
    END IF;
END;
/

Test Cases to Validate #

Let's verify this works for your required scenarios:

• ✅ Matches: '0', '1,2,0,3', '10,2,0', '0,123', '45,0'
• ❌ Doesn't match: '10', '200', '1,10,2', '02,5'

Notes #

• Oracle 11g's REGEXP_LIKE is case-insensitive by default for alphabetic characters, but since we're dealing with digits, this doesn't affect our use case.
• If your input string might have leading/trailing spaces, you can add TRIM() around the input first, or adjust the regex to account for spaces (e.g., '(^|,)\s*0\s*(,|$)' if spaces are allowed around items).

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