PL/SQL正则表达式求助：校验列是否仅包含数字字符

Got it, let's tackle this problem. The issue with to_number() is that it’s designed to parse a valid numeric value from the start of a string (behavior varies slightly by SQL dialect), which means it either ignores trailing non-numeric characters or fails inconsistently. What you need is a strict check that every character in the column is a digit—no letters, symbols, spaces, or anything else allowed. Here’s how to implement this with SQL regular expressions across major dialects:

The core approach is using a regex pattern that matches only strings consisting entirely of digits. Below are implementations for common SQL databases, plus ways to trigger errors when invalid values are found.

1. PostgreSQL #

Use the ~ regex match operator with the pattern ^[0-9]+$ (ensures the entire string is one or more digits).

Add a Check Constraint (Prevent Invalid Inserts/Updates) #

ALTER TABLE your_table
ADD CONSTRAINT chk_column_all_digits
CHECK (your_column ~ '^[0-9]+$');

This will block any non-numeric values from being added or modified in your_column.

Validate Existing Rows & Throw an Error #

-- First, flag invalid rows
SELECT 
    your_column,
    CASE 
        WHEN your_column !~ '^[0-9]+$' THEN 'Invalid: contains non-digit characters'
        ELSE 'Valid'
    END AS validation_result
FROM your_table;

-- Throw an explicit error if invalid values exist
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM your_table WHERE your_column !~ '^[0-9]+$') THEN
        RAISE EXCEPTION 'Error: Non-numeric values found in your_column!';
    END IF;
END $$;

2. Oracle #

Use the REGEXP_LIKE() function with the same strict digit-only pattern.

Check Constraint Example #

ALTER TABLE your_table
ADD CONSTRAINT chk_column_all_digits
CHECK (REGEXP_LIKE(your_column, '^[0-9]+$'));

Validate & Trigger Error #

-- Flag invalid rows
SELECT 
    your_column,
    CASE 
        WHEN NOT REGEXP_LIKE(your_column, '^[0-9]+$') THEN 'Invalid: contains non-digit characters'
        ELSE 'Valid'
    END AS validation_result
FROM your_table;

-- Throw an error for invalid entries
DECLARE
    invalid_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO invalid_count
    FROM your_table
    WHERE NOT REGEXP_LIKE(your_column, '^[0-9]+$');
    
    IF invalid_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Found ' || invalid_count || ' non-numeric values in your_column!');
    END IF;
END;
/

3. MySQL/MariaDB #

Use the REGEXP operator (or RLIKE, its equivalent) with ^[0-9]+$.

Check Constraint Example #

ALTER TABLE your_table
ADD CONSTRAINT chk_column_all_digits
CHECK (your_column REGEXP '^[0-9]+$');

Validate & Throw Error (Using a Stored Procedure) #

-- Flag invalid rows
SELECT 
    your_column,
    CASE 
        WHEN your_column NOT REGEXP '^[0-9]+$' THEN 'Invalid: contains non-digit characters'
        ELSE 'Valid'
    END AS validation_result
FROM your_table;

-- Create procedure to check and throw error
DELIMITER //
CREATE PROCEDURE check_numeric_column()
BEGIN
    DECLARE invalid_count INT;
    SELECT COUNT(*) INTO invalid_count
    FROM your_table
    WHERE your_column NOT REGEXP '^[0-9]+$';
    
    IF invalid_count > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('Error: Found ', invalid_count, ' non-numeric values in your_column!');
    END IF;
END //
DELIMITER ;

-- Run the check
CALL check_numeric_column();

Key Notes #

• The regex ^[0-9]+$ breaks down to:
  • ^: Start of the string
  • [0-9]+: One or more digits (0-9)
  • $: End of the string
• If you need to allow negative numbers or decimals, adjust the regex (e.g., ^-?[0-9]+(\.[0-9]+)?$ for optional negatives and decimals), but this pattern fits your original requirement of blocking any non-digit characters.
• to_number() fails here because it’s a conversion function, not a validator—it only cares if it can extract a numeric value from the string, not if the entire string is numeric. Regex gives you strict, consistent control over what’s allowed.

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