SQL Server 2017只读权限下无regexp_match函数时，如何从两列提取序列号

Got it, let's tackle this problem step by step since you're working with SQL Server 2017 (no regexp_match like PostgreSQL) and only have read-only access. First, let's fix the issue with your earlier PATINDEX attempt—you were returning the index value instead of using it to extract the substring, and you didn't handle cases where SN: isn't present (which is why PATINDEX returns 0).

Solution Query #

Here's a complete query that matches your expected output, using only built-in SQL Server functions (no custom functions needed):

DECLARE @t TABLE (a nvarchar(255), b nvarchar(255))
insert into @t values
('1.2.344', 'blah blah SN: A123Z4567, blah blah')
, ('B2234567', 'irrelevant stuff not a serial 123')
, ('1.2.345', 'blah blah (SN: C32ZZ4567, blah) blah')
, ('1.2.345', 'blah blah (No Serial at all here blah) blah')
, ('', '');

SELECT 
    a,
    b,
    serialnumber = COALESCE(
        -- First check if column 'a' is a valid serial (non-empty, only letters/numbers)
        CASE WHEN a <> '' AND a NOT LIKE '%[^0-9A-Z]%' THEN a END,
        -- Then extract serial from column 'b' after "SN: " up to the next comma (or end of string)
        CASE WHEN PATINDEX('%SN: %', b) > 0 THEN
            SUBSTRING(
                b,
                PATINDEX('%SN: %', b) + 4, -- Start right after "SN: " (4 characters total)
                CASE 
                    -- If a comma exists after the SN, calculate length up to that comma
                    WHEN CHARINDEX(',', b, PATINDEX('%SN: %', b) + 4) > 0 
                        THEN CHARINDEX(',', b, PATINDEX('%SN: %', b) + 4) - (PATINDEX('%SN: %', b) + 4)
                    -- If no comma, take the rest of the string starting after "SN: "
                    ELSE LEN(b) - (PATINDEX('%SN: %', b) + 4) + 1
                END
            )
        END
    )
FROM @t;

How This Works #

Let's break down the logic:

1. Column 'a' Check: The CASE statement uses a NOT LIKE '%[^0-9A-Z]%' to ensure 'a' only contains uppercase letters and numbers (excluding values like 1.2.3 which have decimal points). We also check a <> '' to skip empty strings.
2. Column 'b' Extraction:
   • PATINDEX('%SN: %', b) finds the starting position of the "SN: " pattern. If it returns >0, we know the serial exists here.
   • SUBSTRING starts at PATINDEX(...) + 4 to skip past "SN: ".
   • The inner CASE handles two scenarios:
     • If a comma exists after the serial, we calculate the length up to that comma to avoid including extra text.
     • If no comma exists, we take the remaining part of the string starting after "SN: ".
3. COALESCE: Prioritizes the valid serial from 'a' first, then falls back to the extracted value from 'b', and returns NULL if neither is valid.

Bonus: Handle Variations of "SN:" #

If your data might have SN: without a space (e.g., SN:A123) or extra spaces (e.g., SN: A123), adjust the PATINDEX pattern to '%SN:%' and use LTRIM to clean up leading spaces:

CASE WHEN PATINDEX('%SN:%', b) > 0 THEN
    LTRIM(SUBSTRING(
        b,
        PATINDEX('%SN:%', b) + 3, -- Start after "SN:" (3 characters)
        CASE 
            WHEN CHARINDEX(',', b, PATINDEX('%SN:%', b) + 3) > 0 
                THEN CHARINDEX(',', b, PATINDEX('%SN:%', b) + 3) - (PATINDEX('%SN:%', b) + 3)
            ELSE LEN(b) - (PATINDEX('%SN:%', b) + 3) + 1
        END
    ))
END

Test Results #

Running the first query against your sample data will produce exactly the expected output:

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