SQL Server 2014中从分隔字符串提取编号并关联OrderLines与Production表

Got it, let's tackle this problem step by step. You need to join OrderLines with Production by extracting the Production Entry number from the messy GeneratedEntries field in SQL Server 2014—here's how to do it properly:

1. Basic Extraction & Join (Single Entry per OrderLine) #

First, let's assume each GeneratedEntries value has one valid Production Entry (starting with I followed by numbers, possibly with separators like - or spaces). We'll use PATINDEX to locate the starting point of the number and SUBSTRING to pull it out.

Example Query #

SELECT 
    ol.OrderLineID,
    ol.ProductID,
    p.ProductionID,
    p.BOMDetails
FROM 
    OrderLines ol
LEFT JOIN 
    Production p 
    ON CAST(
        -- Extract the numeric part after the first "I" that's followed by a number
        SUBSTRING(
            ol.GeneratedEntries,
            -- Find position of first "I" + digit combo, then move past the "I"
            PATINDEX('%I[0-9]%', ol.GeneratedEntries) + 1,
            -- Extract until the first non-digit character (add a space to handle end-of-string)
            PATINDEX(
                '%[^0-9]%',
                SUBSTRING(ol.GeneratedEntries, PATINDEX('%I[0-9]%', ol.GeneratedEntries) + 1, LEN(ol.GeneratedEntries)) + ' '
            ) - 1
        ) AS INT
    ) = p.ProductionEntryNumber
WHERE 
    ol.GeneratedEntries IS NOT NULL 
    AND PATINDEX('%I[0-9]%', ol.GeneratedEntries) > 0; -- Only include rows with valid entries

Breakdown of the Extraction Logic #

• PATINDEX('%I[0-9]%', ol.GeneratedEntries): Finds the first occurrence of I immediately followed by a digit—this ensures we target a valid Production Entry start.
• The inner SUBSTRING grabs everything after that I, then we use another PATINDEX to find the first non-digit character, so we only keep the numeric part.
• CAST(...) AS INT: Converts the extracted string to an integer to match the ProductionEntryNumber (adjust this to VARCHAR if your Production Entry number is stored as text).

2. Handling Multiple Entries per OrderLine #

If GeneratedEntries can have multiple Production Entry numbers (e.g., I123,I456 or I-789 I012), we need to split the field first. Since SQL Server 2014 doesn't have STRING_SPLIT, we'll create a simple split function:

Step 1: Create the Split Function #

CREATE FUNCTION dbo.SplitString (@InputString NVARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @OutputTable TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @StartIndex INT = 1, @EndIndex INT;

    -- Add delimiter to end if missing, to simplify loop logic
    IF RIGHT(@InputString, 1) <> @Delimiter
        SET @InputString += @Delimiter;

    WHILE CHARINDEX(@Delimiter, @InputString) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString);
        INSERT INTO @OutputTable(Value)
        SELECT SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex);
        SET @InputString = SUBSTRING(@InputString, @EndIndex + 1, LEN(@InputString));
    END

    RETURN;
END

Step 2: Split & Join Query #

WITH SplitEntries AS (
    SELECT 
        ol.OrderLineID,
        ol.ProductID,
        s.Value AS RawEntry
    FROM 
        OrderLines ol
    CROSS APPLY 
        dbo.SplitString(ol.GeneratedEntries, ',') s -- Use your actual delimiter (comma, space, etc.)
    WHERE 
        ol.GeneratedEntries IS NOT NULL
)
SELECT 
    se.OrderLineID,
    se.ProductID,
    p.ProductionID,
    p.BOMDetails
FROM 
    SplitEntries se
LEFT JOIN 
    Production p 
    ON CAST(
        SUBSTRING(
            se.RawEntry,
            PATINDEX('%I[0-9]%', se.RawEntry) + 1,
            PATINDEX('%[^0-9]%', SUBSTRING(se.RawEntry, PATINDEX('%I[0-9]%', se.RawEntry) + 1, LEN(se.RawEntry)) + ' ') - 1
        ) AS INT
    ) = p.ProductionEntryNumber
WHERE 
    PATINDEX('%I[0-9]%', se.RawEntry) > 0;

3. Performance Optimization #

If your OrderLines table is large, the string manipulation can slow down queries. Add a persisted computed column to pre-extract the Production Entry number(s):

For Single Entries #

ALTER TABLE OrderLines
ADD ProductionEntryNumber AS CAST(
    SUBSTRING(
        GeneratedEntries,
        PATINDEX('%I[0-9]%', GeneratedEntries) + 1,
        PATINDEX('%[^0-9]%', SUBSTRING(GeneratedEntries, PATINDEX('%I[0-9]%', GeneratedEntries) + 1, LEN(GeneratedEntries)) + ' ') - 1
    ) AS INT
) PERSISTED;

-- Create an index on the computed column to speed up joins
CREATE NONCLUSTERED INDEX IX_OrderLines_ProductionEntryNumber ON OrderLines(ProductionEntryNumber);

Then your join becomes much simpler:

SELECT 
    ol.OrderLineID,
    ol.ProductID,
    p.ProductionID,
    p.BOMDetails
FROM 
    OrderLines ol
LEFT JOIN 
    Production p ON ol.ProductionEntryNumber = p.ProductionEntryNumber
WHERE 
    ol.ProductionEntryNumber IS NOT NULL;

Notes to Test #

• Adjust the PATINDEX patterns if your GeneratedEntries has edge cases (e.g., GeneratedEntries = '生产单编号:I-1234'—%I[^0-9]*[0-9]% would handle non-digits between I and the number).
• If ProductionEntryNumber is a string (not integer), remove the CAST(...) AS INT part and match directly as text.

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