By a strange quirk of fate I have not really had to do too much with CLR functions in SQL Server until I started hanging out on Twitter.  Then I saw this question..

“How do I remove non-numeric characters from a VARCHAR?”

Easy I thought until I realised that T-SQL isn’t really that good with string functions. Anyway I have cracked it but it isn’t going to be fast against a huge table, because I have a loop inside a scalar function ..

ALTER FUNCTION production.ufnChartoNumeric
(
@String nvarchar(50)
)
RETURNS int
AS
BEGIN
    
    DECLARE @CleanNumber int   -- the final product
    DECLARE @CleanString nvarchar(50) ='' -- the inputstring with only numbers in
    DECLARE @StringLength int = len(@String)+1 -- the length of the string +1
    DECLARE @Loop int = 1 -- Loop counter
    -- If the string is already a number we can assign it to the result and finish
    IF isNumeric(@String) = 1
    BEGIN
        SET @CleanString = @String
    END
    ELSE   
    -- If itsn't a number we need to loop through the string a character at a time
    -- and test if each character is a number.
    -- if it is append it to our clean string if it isn't move on
    BEGIN
        WHILE @Loop < @StringLength  -- loop for each character in the string
        BEGIN
        IF isNumeric(Substring(@String,@Loop,1)) = 1 SET @CleanString += Substring(@String,@Loop,1)
        SET @Loop += 1 
        END
    END
   -- if there's a decimal point and you want to keep it, (but which one if there are 2!)
    -- you'll need more code to detect it
    -- and maybe return a float, but the first test will keep it
    -- so for this example I want to eliminate it.
    SET @CleanString = Replace(@CleanString, '-','')
    SET @CleanString = Replace(@CleanString, '+','')
    SET @CleanString = Replace(@CleanString,'.','')
    SET @CleanNumber = Cast(@CleanString as int)
    RETURN @CleanNumber
END
GO

As you can see I also had to deal with decimal points and  plus and minus signs as these could occur many times in a string. BTW there are little bits of T-SQL 2008 in here such as ‘+=’ and DECLARE .. = .. which you’ll need to change for older versions.

It does at least work, (I tested it against a few random string and against production.product.productnumber in AdventureWorks), but the only way I can think of to improve the speed is to rewrite it as a CLR which is much better at handling in row string manipulation. 

But I leave that for another day as I am not sure what this is exactly needed for, and often something that works now is better than something fast tomorrow.