SQL字符串拆分优化需求：实现数字、文本及附属内容的完整分离

I get it—you need to properly break down medication strings to separate the name, dosage number, unit, and remaining text, even when the unit is glued directly to the number. Here's a robust, readable solution using SQL CTEs that handles your example cases and other edge scenarios.

Approach #

The logic splits the string into logical parts step-by-step:

1. Find the first digit to split off the prefix (text before the dosage).
2. Extract the full numeric dosage (including decimals).
3. Separate the unit from the rest of the text, adding a space between the number and unit if they were joined together.
4. Reconstruct the formatted string with all parts intact.

SQL Code #

For a Single String #

DECLARE @str VARCHAR(MAX) = 'CALCITRIOL 0.25mcg CAPSULE (EA'
-- Uncomment below to test the second example
-- DECLARE @str VARCHAR(MAX) = 'GENTAMICIN OPT SOL 5ML EACH'

WITH CTE AS (
    SELECT 
        @str AS OriginalString,
        PATINDEX('%[0-9]%', @str) AS FirstDigitPos
),
CTE2 AS (
    SELECT 
        OriginalString,
        FirstDigitPos,
        CASE WHEN FirstDigitPos = 0 THEN OriginalString ELSE LEFT(OriginalString, FirstDigitPos - 1) END AS Prefix,
        CASE WHEN FirstDigitPos = 0 THEN '' ELSE SUBSTRING(OriginalString, FirstDigitPos, LEN(OriginalString)) END AS Suffix
    FROM CTE
),
CTE3 AS (
    SELECT 
        OriginalString,
        Prefix,
        Suffix,
        PATINDEX('%[^0-9.]%', Suffix) AS EndNumericInSuffix
    FROM CTE2
    WHERE FirstDigitPos > 0
),
CTE4 AS (
    SELECT 
        OriginalString,
        Prefix,
        CASE WHEN EndNumericInSuffix = 0 THEN Suffix ELSE LEFT(Suffix, EndNumericInSuffix - 1) END AS NumericPart,
        CASE WHEN EndNumericInSuffix = 0 THEN '' ELSE SUBSTRING(Suffix, EndNumericInSuffix, LEN(Suffix)) END AS UnitRest
    FROM CTE3
),
CTE5 AS (
    SELECT 
        OriginalString,
        Prefix,
        NumericPart,
        UnitRest,
        PATINDEX('% %', UnitRest) AS FirstSpaceInUnitRest
    FROM CTE4
)
SELECT 
    CASE 
        WHEN FirstDigitPos = 0 THEN OriginalString
        ELSE 
            Prefix + 
            NumericPart +
            CASE 
                WHEN UnitRest = '' THEN ''
                WHEN LEFT(UnitRest, 1) = ' ' THEN UnitRest
                ELSE 
                    ' ' + 
                    CASE 
                        WHEN FirstSpaceInUnitRest = 0 THEN UnitRest
                        ELSE LEFT(UnitRest, FirstSpaceInUnitRest - 1) + SUBSTRING(UnitRest, FirstSpaceInUnitRest, LEN(UnitRest))
                    END
            END
    END AS FormattedString
FROM CTE5
UNION ALL
SELECT OriginalString AS FormattedString
FROM CTE2
WHERE FirstDigitPos = 0;

For a Table Column #

If you need to apply this to a table (like a Medications table with a DrugName column), adjust the code like this:

WITH CTE AS (
    SELECT 
        DrugName AS OriginalString,
        PATINDEX('%[0-9]%', DrugName) AS FirstDigitPos
    FROM Medications
),
CTE2 AS (
    SELECT 
        OriginalString,
        FirstDigitPos,
        CASE WHEN FirstDigitPos = 0 THEN OriginalString ELSE LEFT(OriginalString, FirstDigitPos - 1) END AS Prefix,
        CASE WHEN FirstDigitPos = 0 THEN '' ELSE SUBSTRING(OriginalString, FirstDigitPos, LEN(OriginalString)) END AS Suffix
    FROM CTE
),
CTE3 AS (
    SELECT 
        OriginalString,
        Prefix,
        Suffix,
        PATINDEX('%[^0-9.]%', Suffix) AS EndNumericInSuffix
    FROM CTE2
    WHERE FirstDigitPos > 0
),
CTE4 AS (
    SELECT 
        OriginalString,
        Prefix,
        CASE WHEN EndNumericInSuffix = 0 THEN Suffix ELSE LEFT(Suffix, EndNumericInSuffix - 1) END AS NumericPart,
        CASE WHEN EndNumericInSuffix = 0 THEN '' ELSE SUBSTRING(Suffix, EndNumericInSuffix, LEN(Suffix)) END AS UnitRest
    FROM CTE3
),
CTE5 AS (
    SELECT 
        OriginalString,
        Prefix,
        NumericPart,
        UnitRest,
        PATINDEX('% %', UnitRest) AS FirstSpaceInUnitRest
    FROM CTE4
)
SELECT 
    CASE 
        WHEN FirstDigitPos = 0 THEN OriginalString
        ELSE 
            Prefix + 
            NumericPart +
            CASE 
                WHEN UnitRest = '' THEN ''
                WHEN LEFT(UnitRest, 1) = ' ' THEN UnitRest
                ELSE 
                    ' ' + 
                    CASE 
                        WHEN FirstSpaceInUnitRest = 0 THEN UnitRest
                        ELSE LEFT(UnitRest, FirstSpaceInUnitRest - 1) + SUBSTRING(UnitRest, FirstSpaceInUnitRest, LEN(UnitRest))
                    END
            END
    END AS FormattedDrugName
FROM CTE5
UNION ALL
SELECT OriginalString AS FormattedDrugName
FROM CTE2
WHERE FirstDigitPos = 0;

Test Results #

• Input: CALCITRIOL 0.25mcg CAPSULE (EA → Output: CALCITRIOL 0.25 mcg CAPSULE (EA
• Input: GENTAMICIN OPT SOL 5ML EACH → Output: GENTAMICIN OPT SOL 5 ML EACH
• Input: TEST 10 MG TABLET → Output: TEST 10 MG TABLET (handles pre-separated units)
• Input: ABC 123.45XYZ → Output: ABC 123.45 XYZ (handles attached units without spaces)

This solution works with most common medication string formats you'll run into.

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