SQLTeam.com | Weblogs | Forums

Removing non-numeric and separating numeric within the same field


#1

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.


#2

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!


#3
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