SQL实现：查找字符串中指定字符第二次出现位置的问题

Hey there! Let's get this sorted out for you. The issue you're running into is that your original code is only finding the first occurrence of the character, but you need the second one. Here's how to fix it:

First, let's break down the problem: we need to map each character in your target string TE2ASHEMA (which corresponds to positions 1-9: T=1, E=2, 2=3, A=4, S=5, H=6, E=7, M=8, A=9) to its position, then pick the second occurrence of your input character.

Solution Code #

Here's a SQL script that does exactly what you need:

DECLARE @TargetString VARCHAR(100) = 'TE2ASHEMA'; -- Matches your position sequence
DECLARE @InputChar CHAR(1) = 'E'; -- Replace this with your input character (e.g., 'A')

-- Recursive CTE to split the string into individual characters with their positions
WITH CharPositions AS (
    SELECT 
        1 AS Position,
        SUBSTRING(@TargetString, 1, 1) AS CharValue
    UNION ALL
    SELECT 
        Position + 1,
        SUBSTRING(@TargetString, Position + 1, 1)
    FROM CharPositions
    WHERE Position < LEN(@TargetString)
)
-- Filter for your input character, number its occurrences, and pick the second one
SELECT Position AS SecondOccurrencePosition
FROM (
    SELECT 
        Position,
        ROW_NUMBER() OVER (ORDER BY Position) AS OccurrenceNumber
    FROM CharPositions
    WHERE CharValue = @InputChar
) AS Occurrences
WHERE OccurrenceNumber = 2;

How This Works #

1. Recursive CTE (CharPositions): This splits your target string into a row for each character, paired with its 1-based position. For your string, this will generate 9 rows (one for each character).
2. Number Occurrences: Using ROW_NUMBER(), we assign a number to each occurrence of your input character (ordered by position). The first occurrence gets 1, the second gets 2.
3. Select the Second Occurrence: We filter for rows where OccurrenceNumber = 2 to get the position you need.

Testing It Out #

• If you set @InputChar = 'E', the query returns 7 (the second E's position).
• If you set @InputChar = 'A', it returns 9.
• For characters that only appear once (like 'T' or '2'), the query will return no results (since there's no second occurrence). If you want to handle this case (e.g., return the first occurrence if there's no second), you can adjust the final SELECT to use COALESCE or add a fallback.

Adapting to Your Existing Table #

If you're using your @TBL table that stores the full string, you can modify the script like this:

DECLARE @TBL TABLE (Col VARCHAR(100));
INSERT INTO @TBL VALUES ('TE2ASHEMA');
DECLARE @InputChar CHAR(1) = 'E';

WITH CharPositions AS (
    SELECT 
        1 AS Position,
        SUBSTRING(t.Col, 1, 1) AS CharValue
    FROM @TBL t
    UNION ALL
    SELECT 
        Position + 1,
        SUBSTRING(t.Col, Position + 1, 1)
    FROM CharPositions cp
    JOIN @TBL t ON 1=1
    WHERE Position < LEN(t.Col)
)
SELECT Position AS SecondOccurrencePosition
FROM (
    SELECT 
        Position,
        ROW_NUMBER() OVER (ORDER BY Position) AS OccurrenceNumber
    FROM CharPositions
    WHERE CharValue = @InputChar
) AS Occurrences
WHERE OccurrenceNumber = 2;

This will pull the string directly from your table instead of using a variable.

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