Sql Server Extract String

Question

I have following Table

DECLARE @TABLE TABLE (COL NVARCHAR(MAX))
insert @TABLE values
('[E=110][D=1]'),
('[E=110][D=NE]'),
('[E=110][D=U$]'),
('[E=110][D=FX]')

I am trying to extract data as followed

COL           || EXCEPTION_CODE    ||  DATA
=========================================
[E=110][D=1]  ||     110           || 1
[E=110][D=NE] ||     110           || NE
[E=110][D=U$] ||     110           || U$
[E=110][D=FX] ||     110           || FX

Answer 1

XML gives more flexibility then charindex. replace is very useful to prepare well-formed xml. See code.

;with dat(col, x) as (
select col, cast('<col'+replace(
                         replace(
                           replace(col,'=','="'),
                         ']','" '),
                        '[',' ')+' />' as xml)
from @TABLE
)
select col, t.v.value('@E','int') Exception_code, t.v.value('@D','varchar(100)') [DATA]
from dat cross apply x.nodes('col') t(v)

And results are as desired in OP.