Split brackets into columns - SSMS

Question

I have a string value [a1.1]+[a2.1]+[a3.1]+[a4.1]

I need the string value within each bracket to show in a single column but have no idea how to approach this. Any help would be appreciated

An example of the output that i need is below

a1.1 a2.1 a3.1 a4.1

score 0 · Answer 1 · answered Feb 17 '19 at 03:06

Using DelimitedSplit8K you could do this:

SELECT ItemNumber = s.ItemNumber-1, Item = SUBSTRING(s.item,0,CHARINDEX(']',s.item))
FROM   dbo.DelimitedSplit8K('[a1.1]+[a2.1]+[a3.1]+[a4.1]','[') AS s
WHERE  s.ItemNumber > 1;

Returns:

ItemNumber  Item
----------- -------
1           a1.1
2           a2.1
3           a3.1
4           a4.1

Split brackets into columns - SSMS

1 Answers1