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 |