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.