SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE multi_string_db (
    multi_string nvarchar (256)
)

INSERT multi_string_db
VALUES
      ('[Additional time required, Time requested]')
    , ('[Additional time required, Document requested]')
    , ('[Additional time required, Missing documents - Personal, Other]')
    , ('[Additional time required, Missing documents - Personal]')
    , ('Additional time required')
    , ('Document Requested')
    , ('Missing FPA/evidence')
    , ('Missing documents - Office')
    , ('Missing documents - Personal')
    , ('Other')
    , ('Referred to Decision Maker Team')
    , ('Target date error')
    -- A few extra test cases
    , ('[Single Value list]')
    , ('[]') -- Empty list 1 (treated as one empty value)
    , ('') -- Empty 2
    , ('[abc, def') -- Non-conforming 1
    , ('abc, def]') -- Non-conforming 2
    , ('abc, [def], ghi') -- Non-conforming 3
    , ('not, a, bracketed, list')
19 rows affected

SELECT M.multi_string,
    CASE WHEN M.multi_string LIKE '\[%,%\]' ESCAPE '\'
        THEN (
            SELECT TRIM(S.value)
            FROM (SELECT SUBSTRING(M.multi_string, 2, LEN(M.multi_string)-2) AS Trimmed) T
            CROSS APPLY STRING_SPLIT(T.Trimmed, ',', 1) S
            WHERE S.ordinal = 2
        )
        ELSE M.multi_string
        END AS Result 
FROM multi_string_db M


multi_string
Result


[Additional time required, Time requested]
Time requested

[Additional time required, Document requested]
Document requested

[Additional time required, Missing documents - Personal, Other]
Missing documents - Personal

[Additional time required, Missing documents - Personal]
Missing documents - Personal

Additional time required
Additional time required

Document Requested
Document Requested

Missing FPA/evidence
Missing FPA/evidence

Missing documents - Office
Missing documents - Office

Missing documents - Personal
Missing documents - Personal

Other
Other

Referred to Decision Maker Team
Referred to Decision Maker Team

Target date error
Target date error

[Single Value list]
[Single Value list]

[]
[]




[abc, def
[abc, def

abc, def]
abc, def]

abc, [def], ghi
abc, [def], ghi

not, a, bracketed, list
not, a, bracketed, list


SELECT M.multi_string,
    CASE WHEN M.multi_string LIKE '\[%\]' ESCAPE '\'
        THEN (
            SELECT TOP 1 TRIM(S.value)
            FROM (SELECT SUBSTRING(M.multi_string, 2, LEN(M.multi_string)-2) AS Trimmed) T
            CROSS APPLY STRING_SPLIT(T.Trimmed, ',', 1) S
            WHERE S.ordinal <= 2
            ORDER BY S.ordinal DESC
        )
        ELSE M.multi_string
        END AS Result 
FROM multi_string_db M


multi_string
Result


[Additional time required, Time requested]
Time requested

[Additional time required, Document requested]
Document requested

[Additional time required, Missing documents - Personal, Other]
Missing documents - Personal

[Additional time required, Missing documents - Personal]
Missing documents - Personal

Additional time required
Additional time required

Document Requested
Document Requested

Missing FPA/evidence
Missing FPA/evidence

Missing documents - Office
Missing documents - Office

Missing documents - Personal
Missing documents - Personal

Other
Other

Referred to Decision Maker Team
Referred to Decision Maker Team

Target date error
Target date error

[Single Value list]
Single Value list

[]





[abc, def
[abc, def

abc, def]
abc, def]

abc, [def], ghi
abc, [def], ghi

not, a, bracketed, list
not, a, bracketed, list

multi_string	Result
[Additional time required, Time requested]	Time requested
[Additional time required, Document requested]	Document requested
[Additional time required, Missing documents - Personal, Other]	Missing documents - Personal
[Additional time required, Missing documents - Personal]	Missing documents - Personal
Additional time required	Additional time required
Document Requested	Document Requested
Missing FPA/evidence	Missing FPA/evidence
Missing documents - Office	Missing documents - Office
Missing documents - Personal	Missing documents - Personal
Other	Other
Referred to Decision Maker Team	Referred to Decision Maker Team
Target date error	Target date error
[Single Value list]	[Single Value list]
[]	[]

[abc, def	[abc, def
abc, def]	abc, def]
abc, [def], ghi	abc, [def], ghi
not, a, bracketed, list	not, a, bracketed, list