The case:
We have a log table that include Message column (nvarchar).
We need to extract from the message some data, for example: Entity ID and Entity Type.
SELECT LogId,
(case when CHARINDEX ('EntityID:', Msg) > 0
then substring ( Msg,
CHARINDEX ('EntityID:', Msg) + 9, -- 9=length of 'EntityID:'
( case when CHARINDEX ('EntityID:', Msg) > 0
then ( CHARINDEX (',', Msg, CHARINDEX ('EntityID:', Msg))
- CHARINDEX ('EntityID:', Msg) - 9) -- 9=length of 'EntityID:'
else CHARINDEX ('EntityID:', Msg) + 9 -- 9=length of 'EntityID:'
end
)
)
else NULL
end ) as EntityID,
(case when CHARINDEX ('EntityType:', Msg) > 0
then substring ( Msg,
CHARINDEX ('EntityType:', Msg) + 11, -- 11=length of 'EntityID:'
( case when CHARINDEX ('EntityType:', Msg) > 0
then ( CHARINDEX (',', Msg, CHARINDEX ('EntityType:', Msg))
- CHARINDEX ('EntityType:', Msg) - 11) -- 11=length of 'EntityID:'
else CHARINDEX ('EntityType:', Msg) + 11 -- 11=length of 'EntityID:'
end
)
)
else NULL
end ) as EntityType
FROM OurLogTable
