I've got an error table (generated from an SSIS package) that contains a number of columns relating to the process IDs, machine name, timestamp, etc., but inexplicitly, all of the error information was written as a string into a varchar(max) field. Here is an example of what could be contained in the field:
[ErrorCode] =-1071607778 [ErrorColumn]=0 [ErrorDescription]=Lookup Seller 'seller name' Error; Row yielded no match during lookup.
[Error File]: 'error file name goes here'
Using T-SQL, I need to be able to split the different elements - for example, a SELECT query that returns '[Error Code]', '[Error Description]' and '[Error File]' as separate fields for grouping. Obviously, most of these elements are variable length within the field, and even the '[Error Code]' section doesn't always start in position 1.
(Not an option to go back to SSIS developers to split out field, unfortunately.)
Using MSSQL 2014, Windows 10.