如何在SQL中验证0000/00000格式的字符串？

Hey there! Let's get that string validation logic sorted out for the 0000/00000 format. First, let's break down the issues in your current code, then rewrite it to work correctly.

Issues in Your Current Code #

• Variable Length Mistake: When you declare @ID nvarchar without a length, SQL Server defaults to a length of 1. That means your test value '0000/00000' gets truncated to just '0' — totally breaking the validation. Always specify a length for nvarchar (we'll use nvarchar(12) here, since our valid string is only 10 characters long).
• Reversed CASE Logic: Right now, you're returning 'OK' when the string is invalid and 'ERROR' when it's valid. That's backwards from what you'd want!
• Overly Broad Length Check: Checking len(@id) not between 1 and 12 is too vague. We know the valid format is exactly 10 characters (4 digits + 1 slash + 5 digits), so we can check for that exact length.
• Invalid Wildcard Condition: The LEFT(@id,13) LIKE '%[0-9]&' part doesn't make sense — & isn't a valid wildcard here, and checking the first 13 characters is unnecessary for a 10-character valid string.

Fixed Validation Code #

Here's the corrected version that properly validates the 0000/00000 format:

DECLARE @ID nvarchar(12) = '0000/00000' -- Specify length to avoid truncation
SELECT 
    CASE 
        WHEN LEN(@ID) = 10 -- Exact length required for valid format
             AND @ID LIKE '[0-9][0-9][0-9][0-9]/[0-9][0-9][0-9][0-9][0-9]' -- Enforce 4 digits + slash + 5 digits
        THEN 'OK' -- Valid string returns OK
        ELSE 'ERROR' -- Invalid string returns ERROR
    END AS ValidationResult

Simplified Version (SQL Server 2017+) #

If you're using SQL Server 2017 or later, you can use PATINDEX for a more concise pattern (it supports regex-style quantifiers):

DECLARE @ID nvarchar(12) = '0000/00000'
SELECT 
    CASE 
        WHEN LEN(@ID) = 10
             AND PATINDEX('%[0-9]{4}/[0-9]{5}%', @ID) = 1 -- Shorter pattern for same rule
        THEN 'OK'
        ELSE 'ERROR'
    END AS ValidationResult

How It Works #

• Exact Length Check: LEN(@ID) = 10 ensures we only consider strings of the correct length first, which is a quick preliminary check.
• Pattern Matching: The LIKE (or PATINDEX) pattern strictly enforces the format: 4 numeric digits, followed by a slash, followed by 5 numeric digits. No extra characters allowed before or after.
• Correct Logic Flow: Valid strings return 'OK', invalid ones return 'ERROR' — matching the expected behavior.

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