SQL解析格式混乱的Plex ERP数据库字符串以提取带小数的尺寸数值

Since Plex ERP runs on SQL Server, which doesn’t support full regular expressions, we can leverage built-in string functions like PATINDEX, SUBSTRING, and CHARINDEX to accurately pull your dimension values without trailing junk content. Here’s a practical, step-by-step solution:

Step 1: Isolate the Clean Dimension Substring #

Your original query grabs text from the start of the dimension all the way to the end of the string, which includes unwanted trailing content. We’ll fix this by finding the first character after the dimension that isn’t a number, decimal, space, or "x"—that’s where we’ll truncate.

Step 2: Split the Isolated String into Individual Dimensions #

Once we have the clean dimension string (e.g., "2.3 x 340"), we can split it using the " x " separator to get your two numeric values.

Full SQL Query Example #

SELECT
    cpart.Name,
    -- Get the complete, clean dimension string
    SUBSTRING(
        cpart.Name,
        start_pos,
        CASE
            WHEN end_offset = 0 THEN LEN(cpart.Name) - start_pos + 1
            ELSE end_offset - 1
        END
    ) AS full_dimension,
    -- Extract first dimension value
    LTRIM(RTRIM(SUBSTRING(
        clean_dim,
        1,
        CHARINDEX(' x ', clean_dim) - 1
    ))) AS dimension_1,
    -- Extract second dimension value
    LTRIM(RTRIM(SUBSTRING(
        clean_dim,
        CHARINDEX(' x ', clean_dim) + 3,
        LEN(clean_dim)
    ))) AS dimension_2
FROM (
    SELECT
        Name,
        start_pos,
        end_offset,
        -- Precompute the clean dimension substring for reuse
        SUBSTRING(
            Name,
            start_pos,
            CASE WHEN end_offset = 0 THEN LEN(Name)-start_pos+1 ELSE end_offset-1 END
        ) AS clean_dim
    FROM (
        SELECT
            Name,
            -- Find the start of the dimension pattern
            PATINDEX('%[0-9.] x [0-9]%', Name) AS start_pos,
            -- Find the first invalid character after the dimension starts
            PATINDEX('%[^0-9. x]%', SUBSTRING(Name, PATINDEX('%[0-9.] x [0-9]%', Name), LEN(Name))) AS end_offset
        FROM cpart
    ) AS pos_query
    WHERE start_pos > 0 -- Only include rows with a valid dimension
) AS dim_query

How It Handles Your Sample Data #

1. For SPH 270 2.3 x 340 CPS:

   • start_pos locates the start of "2.3 x 340"
   • end_offset finds the position of "C" (the first non-valid character after the dimension)
   • We truncate at that offset to get "2.3 x 340"
   • Split on " x " to return dimension_1 = 2.3 and dimension_2 = 340

2. For SCGA270D-45 1.4 x 486:

   • end_offset returns 0 (no invalid characters after the dimension)
   • We take the full substring from start_pos to the end, getting "1.4 x 486"
   • Split to return dimension_1 = 1.4 and dimension_2 = 486

Key Fixes & Notes #

• Corrected the typo in your original PATINDEX pattern: replaced the en dash [0‐9] with a standard hyphen [0-9] (this was likely causing partial matches)
• LTRIM/RTRIM cleans up any accidental extra spaces around the numbers
• The nested subqueries make the logic cleaner by reusing calculated values instead of repeating complex string operations

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