Stripping Values between two brackets {}-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

You may use STRING_SPLIT() and STUFF() to get the expected results.

Test data:

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

Statement:

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

Result:

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