'Stripping Values between two brackets {}

Good Afternoon,

I'm trying to query a column that gets data between two brackets. there may be multiple sets in the column such as : {Abrasision} {None} {Bruise}

i use this and it doesn't do exactly what i want, because i think i only use one bracket in the query. i want to get each value in my result set and insert into a table variable. Just having a little bit of trouble.

SELECT 
   LEFT(InjuryCategory, CHARINDEX('{', InjuryCategory)-1), 
   SUBSTRING(InjuryCategory, CHARINDEX('{', InjuryCategory)+1, LEN(InjuryCategory)-CHARINDEX('{', InjuryCategory)-CHARINDEX('{',REVERSE(InjuryCategory ))),
   RIGHT(InjuryCategory, CHARINDEX('{', REVERSE(InjuryCategory))-1)  
FROM TblVictim
sqlsql-server


Solution 1:[1]

You may use STRING_SPLIT(), STUFF() and STRING_AGG() to get the expected results. Note, that STRING_SPLIT() orders the results (using enable_ordinal parameter) only in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics (serverless SQL pool only), so STRING_AGG() may aggregate differently.

Test data:

SELECT *
INTO tblVictim
FROM (
   VALUES ('{Abrasision} {None} {Bruise}')
) t (InjuryCategory)

Statement:

SELECT STRING_AGG(STUFF(s.[value], 1, CHARINDEX('{', s.[value]), ''), ' ') AS Category
FROM tblVictim t
CROSS APPLY STRING_SPLIT(t.InjuryCategory, '}') s
WHERE s.[value] <> ''

Result:

Category
----------------------
Abrasision None Bruise

Solution 2:[2]

In newer versions of SQL Server, you can combine STRING_SPLIT and TRIM

SELECT TRIM('{}' FROM s.[value]) AS Category
FROM TblVictim v
CROSS APPLY STRING_SPLIT(v.InjuryCategory, ' ') s
WHERE s.[value] <> '';

db<>fiddle

Solution 3:[3]

Quick and dirty, since this is delimited data, pretend it's XML. Setup:

DECLARE @tblVictim TABLE(ID INT IDENTITY, InjuryCategory NVARCHAR(MAX));
INSERT @tblVictim(InjuryCategory) 
VALUES 
   ('{Abrasision} {None} {Bruise}'),
   ('{Abrasision} {<5} {Bruise; very severe}');

Query:

WITH data AS (
    SELECT ID, xml = CAST(REPLACE(REPLACE(InjuryCategory, 
        '{', '<i><![CDATA['), 
        '}', ']]></i>') AS XML
    )
    FROM @tblVictim
)
SELECT ID, node.value('text()[1]', 'nvarchar(max)')
FROM data
CROSS APPLY xml.nodes('i') AS nodes(node)

Note that this completely breaks down (with no easy fixes) if there are unbalanced delimiters.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Charlieface
Solution 3 Jeroen Mostert

Related Questions