使用SQL正则表达式实现密码复杂度校验的问题排查

Let's get your password validation working as expected. The core issue with your current code is that your LIKE pattern enforces a rigid sequence of characters (uppercase letter → alphanumeric → allowed special character), which is why 1hEl@ fails—it doesn't follow that specific order. Instead, we need to check each required condition independently, since the rules don't care about the order of characters, only that each requirement is met somewhere in the password.

Corrected Code #

DECLARE @password VARCHAR(1000) = '1hEl@'
SELECT 
    CASE 
        WHEN LEN(@password) BETWEEN 7 AND 15 -- Matches "length greater than 6 and less than 16" (7-15 characters)
             AND @password COLLATE Latin1_General_BIN LIKE '%[A-Z]%' -- At least one uppercase letter (case-sensitive check)
             AND @password COLLATE Latin1_General_BIN LIKE '%[0-9]%' -- At least one numeric digit
             AND @password COLLATE Latin1_General_BIN NOT LIKE '%[^A-Za-z0-9!@#$%^&*()]%' -- Restricts to allowed characters (optional but recommended)
        THEN 'Match' 
        ELSE 'Not Match' 
    END AS Output

Key Fixes & Explanations #

Let's break down each part to clarify what's happening:

• Length Check: Your original code used BETWEEN 4 AND 16, but your stated rule requires length >6 and <16. I adjusted this to BETWEEN 7 AND 15 to align with your written requirement. If you actually intended the 4-16 range, just swap that line back—no other changes needed.
• Uppercase Validation: Using Latin1_General_BIN collation ensures we do a case-sensitive check, so we only count actual uppercase A-Z characters (not lowercase or other symbols).
• Number Validation: The %[0-9]% pattern checks for at least one numeric digit anywhere in the password, regardless of where it appears.
• Allowed Characters (Optional): The NOT LIKE '%[^A-Za-z0-9!@#$%^&*()]%' line ensures no characters outside your allowed set are present. If you want to allow any special character (not just the listed ones), you can safely remove this condition—though restricting allowed characters is a common security best practice.

Quick Note on Your Example #

Your test password '1hEl@' is only 5 characters long, which doesn't meet the "length greater than 6" rule. If you expect this password to return Match, you likely meant to set the length requirement to "greater than 4" instead. Just adjust the length condition to LEN(@password) BETWEEN 5 AND 15 or LEN(@password) >4 AND LEN(@password) <16 to make that work.

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