如何从指定格式字符串提取特定子串？求助提取下划线前目标内容

Got it, let's break down how to solve this extraction problem since you already have the suffix part sorted out.

First, let's recap your requirements with the examples:

• For 'H345678_30': We need to grab 5678 (the digits after the 3-character prefix H34 and before the underscore)
• For 'q789038_155': We need 9038 (after prefix q78, before underscore)

Solution Code #

Since your prefix is a fixed 3 characters, we can use SUBSTRING() combined with CHARINDEX() to target exactly the segment we want. Here's the full query, including your existing suffix extraction for completeness:

DECLARE @temp VARCHAR(50) = 'H345678_30' -- Swap with your input string
SELECT
    -- Optional: Extract the 3-character prefix if needed
    PreCode = LEFT(@temp, 3),
    -- The target digits: start at position 4 (skip first 3 chars), length is underscore position minus 4
    TargetNumber = SUBSTRING(@temp, 4, CHARINDEX('_', @temp) - 4),
    -- Your existing code to get the suffix after underscore
    SuffixCode = RIGHT(@temp, CHARINDEX('_', REVERSE(@temp)) - 1)

How It Works #

Let's walk through the TargetNumber calculation with 'H345678_30':

1. CHARINDEX('_', @temp) finds the position of the underscore (which is 8 in this case)
2. We start extracting at position 4 (skipping the first 3 characters of the prefix)
3. The length is 8 - 4 = 4, so we pull 4 characters starting at position 4 → 5678

For the second example 'q789038_155':

• CHARINDEX('_', @temp) returns 8
• SUBSTRING(@temp, 4, 8 - 4) pulls 4 characters starting at position 4 → 9038

Edge Case Note #

If you might have strings that don't follow the exact format (no underscore, prefix not 3 chars), you can add a check to avoid errors, like:

TargetNumber = CASE 
    WHEN CHARINDEX('_', @temp) > 3 THEN SUBSTRING(@temp, 4, CHARINDEX('_', @temp) - 4)
    ELSE NULL -- or handle invalid strings as needed
END

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