如何用SQL提取以英文句点分隔的字符串中第3个字段？

Perfect, let's tweak your existing SUBSTRING logic to pull the 3rd segment efficiently—no CTEs needed, just straightforward built-in functions that’ll perform well for your frequently refreshed dropdown data source.

Here’s the adjusted query: #

SELECT 
    MyColumn,
    -- Keep your existing 1st segment extraction
    LEFT(MyColumn, CHARINDEX('.', MyColumn) - 1) AS [1st Sequence],
    -- Keep your existing 2nd segment extraction
    SUBSTRING(MyColumn, CHARINDEX('.', MyColumn) + 1 , CHARINDEX('.', MyColumn, CHARINDEX('.', MyColumn)+1) - (CHARINDEX('.', MyColumn) + 1)) AS [2nd Sequence],
    -- New: Extract the 3rd segment
    SUBSTRING(
        MyColumn,
        -- Start right after the second dot
        CHARINDEX('.', MyColumn, CHARINDEX('.', MyColumn) + 1) + 1,
        -- Calculate length: use third dot position if it exists, else go to end of string
        COALESCE(
            NULLIF(CHARINDEX('.', MyColumn, CHARINDEX('.', MyColumn, CHARINDEX('.', MyColumn)+1)+1), 0) - (CHARINDEX('.', MyColumn, CHARINDEX('.', MyColumn)+1) + 1),
            LEN(MyColumn) - CHARINDEX('.', MyColumn, CHARINDEX('.', MyColumn)+1)
        )
    ) AS [3rd Sequence]
FROM dbo.MyTable;

How it works (with your examples): #

• Example 1: Input = 2345.Z2Z2.A12151.AB4R
  • The second dot sits at position 10, so we start extracting at position 11.
  • The third dot is at position 17, so we calculate the length as 17 - 11 = 6, pulling exactly A12151.
• Example 2: Input = STR4.35S982F.X24
  • The second dot is at position 12, so we start extracting at position 13.
  • There’s no third dot, so CHARINDEX returns 0. COALESCE falls back to using the total string length minus the second dot position, giving a length of 15 - 12 = 3 to pull X24.

Key notes: #

• Uses only lightweight, built-in functions that won’t add overhead for frequent refreshes.
• Handles both scenarios: when the 3rd segment is followed by more dots, or when it’s the final segment in the string.

内容的提问来源于stack exchange，提问作者Harambe Attack Helicopter