I have a field that contains results similar to the below.
The Execute call (on DbLoader) has finished.
4428191 row(s) were read from input.
0 total row(s) rejected.
4428191 total row(s) loaded.
Total duration was: 00:16:19.03
What I am trying to do is remove all of the non-numeric data, but I also need to separate the numbers into individual columns. So the result would be something similar to the below
col1 [Records Read] - 4428191
col2 [Records Rejected] - 0
col3 [Records Loaded] - 4428191
col4 [Total Duration] 00:16:19.03
I found a function online that will effectively remove all non-numeric data, but what I end up with is a string of all of the numbers and no spaces or other semaphore in between that I can then use to separate the numbers into their respective columns.
Do all the rows that you will get match specific patterns? or is the text on each row basically "random"?
If the first I would set up specific pattern matches for each expected text, and then based on each match parse the number from the relevant part of the data. I would also have an error-catch for any unexpected text patterns - so that any that do occur, now or in the future, do not go unnoticed!
SELECT string,
CASE WHEN byte#_of_first_digit_in_string = 0 THEN ''
ELSE SUBSTRING(string, byte#_of_first_digit_in_string,
byte#_of_last_digit_in_string - byte#_of_first_digit_in_string + 1)
END AS numeric_string
FROM (
VALUES('4428191 row(s) were read from input.'),('0 total row(s) rejected.'),
('4428191 total row(s) loaded.'),('Total duration was: 00:16:19.03'),
('string with no digit in it!'), ('what if 2 or 6 or 12 appears in string?')
) strings(string)
CROSS APPLY (
SELECT PATINDEX('%[0-9]%', string) AS byte#_of_first_digit_in_string,
LEN(string) - PATINDEX('%[0-9]%', REVERSE(string)) + 1 AS byte#_of_last_digit_in_string
) AS assign_alias_names_1