Posts Tagged mssql
How to parse strings to numbers in T-SQL
Posted by martiendejong in Programming, SQL on December 2, 2009
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:
Or here:
http://www.ibm.com/developerworks/data/library/techarticle/0303stolze/0303stolze1.html