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:

[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.
Donovan MooreAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
Here is an approach, using a MS SQL 2017 function called STRING_SPLIT() . In your version of SQL Server (2014) you will need a user defined "split string" function instead (and there are many widely/freely available).  The following is intended as a guide on how to re-arrange the string into consistent columns.

Sample data used:
CREATE TABLE Table1
    ([id] int, [bigstring] varchar(max))
;
  
INSERT INTO Table1
    ([id], [bigstring])
VALUES
    (1, '[ErrorCode] =-1071607778 [ErrorColumn]=0 [ErrorDescription]=Lookup Seller ''seller name'' Error; Row yielded no match during lookup.  [Error File]: ''error file name goes here'''),
    (2, '[ErrorColumn]=0 [ErrorDescription]=Lookup Seller ''seller name'' Error; Row yielded no match during lookup.  [Error File]: ''error file name goes here'' [ErrorCode] =-1071607778'),
    (3, '[Error File]: ''error file name goes here'' [ErrorCode] =-1071607778 [ErrorColumn]=0 [ErrorDescription]=Lookup Seller ''seller name'' Error; Row yielded no match during lookup.'),
    (4, '[ErrorCode] =-1071607778 [Error File]: ''error file name goes here'' [ErrorColumn]=0 [ErrorDescription]=Lookup Seller ''seller name'' Error; Row yielded no match during lookup.')
;

Open in new window

select
       id
     , ordby
     , left(partstring,charindex(']', partstring)) col_name
     , substring(partstring,pos,len(partstring)) col_value
from (
      select 
             id
           , value AS partstring
           , dense_rank() over(partition by id order by value) ordby
             /* get the starting point of the actual error value, used in subsring function later */
           , coalesce(
                nullif(charindex('] = ', value),0)+4
               ,nullif(charindex('] =', value),0)+3
               ,nullif(charindex(']=', value),0)+2
               ,nullif(charindex(']: ', value),0)+3
               ,nullif(charindex(']:', value),0)+2
               ,charindex(']', value + ']')
            ) AS pos
      from table1
      cross apply string_split(bigstring,'[')
    ) d
where partstring like 'Error%' 

Open in new window

The result of that query:
+----+-------+-------------------+------------------------------------------------------------------------+
| id | ordby |     col_name      |                               col_value                                |
+----+-------+-------------------+------------------------------------------------------------------------+
|  1 |     2 | Error File]       | 'error file name goes here'                                            |
|  1 |     3 | ErrorCode]        | -1071607778                                                            |
|  1 |     4 | ErrorColumn]      | 0                                                                      |
|  1 |     5 | ErrorDescription] | Lookup Seller 'seller name' Error; Row yielded no match during lookup. |
|  2 |     2 | Error File]       | 'error file name goes here'                                            |
|  2 |     3 | ErrorCode]        | -1071607778                                                            |
|  2 |     4 | ErrorColumn]      | 0                                                                      |
|  2 |     5 | ErrorDescription] | Lookup Seller 'seller name' Error; Row yielded no match during lookup. |
|  3 |     2 | Error File]       | 'error file name goes here'                                            |
|  3 |     3 | ErrorCode]        | -1071607778                                                            |
|  3 |     4 | ErrorColumn]      | 0                                                                      |
|  3 |     5 | ErrorDescription] | Lookup Seller 'seller name' Error; Row yielded no match during lookup. |
|  4 |     2 | Error File]       | 'error file name goes here'                                            |
|  4 |     3 | ErrorCode]        | -1071607778                                                            |
|  4 |     4 | ErrorColumn]      | 0                                                                      |
|  4 |     5 | ErrorDescription] | Lookup Seller 'seller name' Error; Row yielded no match during lookup. |
+----+-------+-------------------+------------------------------------------------------------------------+

Open in new window

Notes:
dense_rank has been used to give a consistent order to the result, it probably isn't necessary
nullif(,0) is used to ignore a charindex return of zero, and coalesce is then used to grab the first non-null value

see it working at: http://sqlfiddle.com/#!18/f49e9/2

some links to use defined functions for splitting strings:
https://sqlperformance.com/2012/07/t-sql-queries/split-strings
http://www.sqlservercentral.com/articles/Tally+Table/72993/
0
 
Bill PrewCommented:
Can you upload (as an attachment) a larger sample of the real file data?

Is it true that these strings are always in the large text string, and you want the data between / following them?

  • [ErrorCode]
  • [ErrorColumn]
  • [ErrorDescription]
  • [Error File]

Do you need a purely SQL approach, or could you add a stored procedure to your schema with a function that did this?


»bp
0
All Courses

From novice to tech pro — start learning today.