T-SQL: Parse Column Into Separate Elements
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:
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.
[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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is it true that these strings are always in the large text string, and you want the data between / following them?
Do you need a purely SQL approach, or could you add a stored procedure to your schema with a function that did this?
»bp