SQL Server保单号码格式实现求助：多种指定格式需求解决

First, let's clarify the pattern from your examples: every valid policy number has two space-separated components:

• The first part is a numeric string (digits only) with a length of 2, 4, 6, 8, or 9
• The second part is the uppercase letter A repeated exactly the same number of times as the first part's length

Below are practical solutions for both validating existing strings and generating new policy numbers that fit this format.

1. Validate Existing Strings Against the Format #

If you need to check whether existing policy numbers in your table match the required pattern, use one of these approaches based on your SQL Server version:

For SQL Server 2016 and Later (Supports REGEXP_LIKE) #

Regular expressions make this validation clean and concise:

SELECT 
    PolicyNumber,
    CASE 
        WHEN REGEXP_LIKE(PolicyNumber, '^(\d{2}|\d{4}|\d{6}|\d{8}|\d{9}) A\1$') 
        THEN 'Valid' 
        ELSE 'Invalid' 
    END AS IsValidPolicyNumber
FROM YourPolicyTable;

Regex Breakdown:

• ^ = Marks the start of the string
• (\d{2}|\d{4}|\d{6}|\d{8}|\d{9}) = Captures a numeric group with one of the allowed lengths
• = Requires a single space separator
• A\1 = Matches the letter A repeated the same number of times as the captured numeric group (the \1 references the first captured group's length)
• $ = Marks the end of the string

For SQL Server 2014 and Earlier (No REGEXP_LIKE) #

Use string functions to manually validate each rule:

SELECT 
    PolicyNumber,
    CASE 
        WHEN CHARINDEX(' ', PolicyNumber) BETWEEN 3 AND 10 -- Ensure space exists at valid position for allowed lengths
             AND PATINDEX('%[^0-9]%', LEFT(PolicyNumber, CHARINDEX(' ', PolicyNumber)-1)) = 0 -- First part is all digits
             AND RIGHT(PolicyNumber, LEN(PolicyNumber)-CHARINDEX(' ', PolicyNumber)) = REPLICATE('A', CHARINDEX(' ', PolicyNumber)-1) -- Second part matches length of first part with 'A's
             AND (CHARINDEX(' ', PolicyNumber)-1) IN (2,4,6,8,9) -- First part length is in allowed set
        THEN 'Valid'
        ELSE 'Invalid'
    END AS IsValidPolicyNumber
FROM YourPolicyTable;

2. Generate New Policy Numbers Matching the Format #

If you need to generate random valid policy numbers, use this script:

-- Generate a single random policy number
DECLARE @SelectedLength INT = (SELECT TOP 1 Length FROM (VALUES(2),(4),(6),(8),(9)) v(Length) ORDER BY NEWID())

SELECT 
    CONCAT(
        -- Generate random numeric string of the selected length (preserves leading zeros)
        RIGHT('0' + CAST(FLOOR(RAND() * POWER(10, @SelectedLength)) AS VARCHAR(9)), @SelectedLength),
        ' ',
        REPLICATE('A', @SelectedLength)
    ) AS NewPolicyNumber;

-- Generate a batch of 15 random policy numbers
WITH AllowedLengths AS (
    SELECT Length FROM (VALUES(2),(4),(6),(8),(9)) v(Length)
),
RandomLengths AS (
    SELECT TOP 15 Length FROM AllowedLengths ORDER BY NEWID()
)
SELECT 
    CONCAT(
        RIGHT('0' + CAST(FLOOR(RAND(CHECKSUM(NEWID())) * POWER(10, rl.Length)) AS VARCHAR(9)), rl.Length),
        ' ',
        REPLICATE('A', rl.Length)
    ) AS NewPolicyNumber
FROM RandomLengths rl;

Note: The RIGHT('0' + ...) ensures that even if the random number has leading zeros (e.g., length 2 gives 05), it's preserved to maintain the correct length.

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