How to parse strings to numbers in T-SQL

If you execute this SQL statement, the function dbo.ParseNumber will be created:

CREATE FUNCTION dbo.ParseNumber
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
END
SET @string = @string
RETURN @string
END
GO

You can now use this function in an SQL statement like this one:

SELECT dbo.ParseNumber(MyStringColumn) FROM MyTable

Or this one:

SELECT * FROM MyTable WHERE dbo.ParseNumber(MyStringColumn) > 10

More information about SQL string parsing can be found here:

http://blog.sqlauthority.com/2007/04/11/sql-server-udf-user-defined-function-to-extract-only-numbers-from-string/

Or here:

http://www.ibm.com/developerworks/data/library/techarticle/0303stolze/0303stolze1.html