Using table and one CTE:
CREATE TABLE TempData
(
LongText VARCHAR(MAX)
);
INSERT INTO TempData (LongText)
VALUES ('Dear {CustomerName},Your invoice ({InvoiceNumber}) has been created. An overview of the invoice is available below:Invoice: {InvoiceNumber}Invoice Date: {InvoiceDate}Amount: {GrandTotal}Please go through it and confirm the invoice. We look forward to working with you again.Regards,Accounts Team{FirmName}')
;WITH CTE AS
(
SELECT 1 AS RN, LongText, CHARINDEX('{', LongText)+1 AS StartPos, CHARINDEX('}', LongText)-1 AS EndPos
FROM TempData
WHERE CHARINDEX('{', LongText)>0 AND CHARINDEX('}', LongText)>0
UNION ALL
SELECT RN + 1 AS RN, LongText, CHARINDEX('{', LongText, StartPos)+1 AS StartPos, CHARINDEX('}', LongText, EndPos+2)-1 AS EndPos
FROM CTE
WHERE CHARINDEX('{', LongText, StartPos)>0 AND CHARINDEX('}', LongText, EndPos+2)>0
)
SELECT RN, SUBSTRING(LongText, StartPos, EndPos - StartPos+1) AS TextBetweenParenthesis
FROM CTE;
Result:
RN TextBetweenParenthesis
1 CustomerName
2 InvoiceNumber
3 InvoiceNumber
4 InvoiceDate
5 GrandTotal
6 FirmName
SQL Server 2019 | db<>fiddle[
^]