好的,我知道这个问题已经有答案了,但是我想展示一个纯T-SQL解决方案,作为另一种选择。
DECLARE @yourTable TABLE (val VARCHAR(100));
INSERT INTO @yourTable
VALUES ('[anything can be here]'),
('[{anything can be here}]'),
('[anything can be here}]'),
('[{anything can be here]');
WITH CTE_Brackets
AS
(
SELECT val,
CASE
WHEN CHARINDEX('{',val) > 0 THEN CHARINDEX('{',val)
END AS L_curly,
CASE
WHEN CHARINDEX('}',val) > 0 THEN CHARINDEX('}',val)
END AS R_curly,
CASE
WHEN CHARINDEX('[',val) > 0 THEN CHARINDEX('[',val)
END AS L_bracket,
CASE
WHEN CHARINDEX(']',val) > 0 THEN CHARINDEX(']',val)
END AS R_bracket
FROM @yourTable
),
CTE_string
AS
(
SELECT val,
L_curly,
R_curly,
L_bracket,
R_bracket,
SUBSTRING(val,start_pos,end_pos - start_pos) val_string
FROM CTE_Brackets A
CROSS APPLY (SELECT COALESCE(L_curly,L_bracket) + 1 AS start_pos,
COALESCE(R_curly,R_bracket) AS end_pos
) CA
)
SELECT A.val,B.val
FROM CTE_string A
INNER JOIN CTE_string B
ON A.val_string = B.val_string
AND
(
(
A.L_curly IS NOT NULL
AND A.R_curly IS NULL
AND B.L_curly IS NULL
AND B.R_curly IS NOT NULL
)
OR
(
A.L_curly + A.R_curly IS NOT NULL
AND B.R_curly IS NULL
AND B.L_curly IS NULL
)
)
ORDER BY B.val
|
,只需使用^\[({)?[^]{}]+(?(1)})]$
就足够了(仅包含 yes 部分)。 - Wiktor Stribiżew