How do you get a numeric-only string and ignore all non-numeric characters, using just standard SQL functions? There’s SQL replace and pattern functions, but they only work once on a string. So barring 53 nested REPLACE functions, it’s tough. I’ve run into this issue many times before while writing import scripts and the like.

Today, I had a stroke of brilliance – what if it could be done using FOR XML and spt_values to parse the entire string? A bit of googling and stack overflowing later, and some gluging of different parts together, and I came up with the following:

SELECT 
    (SELECT CAST(CAST((
        SELECT SUBSTRING(FieldToStrip, Number, 1)
        FROM master..spt_values
        WHERE Type='p' AND Number <= LEN(FieldToStrip) AND
            SUBSTRING(FieldToStrip, Number, 1) LIKE '[0-9]' FOR XML Path(''))
    AS xml) AS varchar(MAX)))
FROM
    SourceTable

Replace SourceTable with your table and FieldToStrip with your field, and away you go. If you want to include other allowed characters, change the pattern in the LIKE -- that specifies which characters to retain. All other fields will be removed.

Comments

Comment by Josh

Brilliant. Sorts out the patindex problem with tel#s nad you don't need a udf. Thanks!

Josh
Comment by Les Stockton

What does Number represent?

I have a data table where there is a field that will have something like iAK0001351. and the non-numerics could be almost anything, so I'd like to strip them off and then produce a MAX to see what the MAX number is.
I think what you have here might do that, but I don't know what Number represents and how to apply that to what I have.

Les Stockton
Comment by Chris Hynes

Number comes from spt_values, which is contains a set of incrementing values. For example:

select top 50 * from master..spt_values where type = 'p' order by number

It's used here to spin through the value character by character and only retain the ones matching the [0-9] pattern.