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
Look at the functions SUBSTRING and CHARINDEX. Using those together you should be able to extract this.
SUBSTRING ( expression ,start , length )
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )
For example:
SELECT SUBSTRING(COL,CHARINDEX('E=',COL)+2,CHARINDEX(']',COL) - CHARINDEX('E=',COL) - 2)
FROM @TABLE
will get you the EXCEPTION_CODE column.
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.
