Extract string between 2nd set of repeating characters

[harishgg1]

hi hope this helps

solved = yes

why am i doing this ? = tally table approach = much more reliable than string split "ordinal"

create table insert data script

-- Sample table
IF OBJECT_ID('dbo.SampleData', 'U') IS NOT NULL
    DROP TABLE dbo.SampleData;

CREATE TABLE SampleData (
    BracketString VARCHAR(50)
);

INSERT INTO SampleData (BracketString)
VALUES
('[TESTDATA][88][6654]'),
('[HELLO][42][9999]'),
('[WORLD][123][4567]'),
('[FOO][77][8888]'),
('[BAR][321][6543]');

-- Tally table approach
;WITH Tally AS (
    SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N  FROM sys.objects
)
SELECT s.BracketString, x.Item AS MiddleValue
FROM SampleData s
CROSS APPLY (
    SELECT Item
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY N) AS Pos,
               SUBSTRING(s.BracketString, N+1, CHARINDEX(']', s.BracketString, N) - N - 1) AS Item
        FROM Tally
        WHERE SUBSTRING(s.BracketString, N, 1) = '['
    ) t
    WHERE Pos = 2
) x;