TSQL - A Better INT Conversion Function

Question

I'm wondering if there is a better way to 'parse' a Varchar to an Int in TSQL / SQL Server. I say 'parse' because I need something more robust than the CAST/CONVERT system funcs; it's particularly useful to return NULL when the parse fails, or even a 'default' value.

So here's the function I'm using now, originally obtained from someone's SQL blog (can't even remember specifically who)...

ALTER FUNCTION [dbo].[udf_ToNumber]
(
    @Str varchar(max)
)
RETURNS int
AS
BEGIN
    DECLARE @Result int

    SET @Str = LTRIM(RTRIM(@Str))

    IF (@Str='' OR @Str IS NULL
        OR ISNUMERIC(@Str)=0
        OR @Str LIKE '%[^-+ 0-9]%'
        OR @Str IN ('.', '-', '+', '^') 
        )
        SET @Result = NULL
    ELSE
    IF (CAST(@Str AS NUMERIC(38,0)) NOT BETWEEN -2147483648. AND 2147483647.)
        SET @Result = NULL
    ELSE
        SET @Result = CAST(@Str AS int)

    RETURN @Result
END

(And you could add a line before the end, like "if @Result is null, set @Result = ", or something like that).

It's not very efficient, because using it in a JOIN or WHERE-IN-SELECT -- where say the LEFT column is INT and the RIGHT is VARCHAR, and I try to parse the RIGHT -- on any significantly large data-set, takes a lot longer than if I CAST the LEFT (INT) column to a VARCHAR first and then do the JOIN.

Anyway, I know 'ideally' that I shouldn't need to do this kind of thing in the first place if my tables/data-types are created & populated appropriately, but we all know the ideal world is very far from reality sometimes, so humor me. Thanks!

EDIT: SQL Server versions 2005 & 2008; boxes running 2005 will be upgraded soon so 2008-specific answers are fine.

SQL Server 2012 will have things like TRY_CONVERT and TRY_PARSE - but before that, you'd have to roll your own (e.g. in C# using the SQL-CLR facility) — marc_s
Perhaps you could stored the numeric value in a persisted calculated column, so the conversion only happens on insert or update? — ulty4life
I looked up "persisted computed column" and I do see the value in that; however in my case, the table with the 'offending' values already exists and contains millions of rows. So adding such a column would, presumably, still take a long time to populate its values, if the 'formula' for the computed-column was the same. Unless I'm wrong -- is SQL Server magically more efficient when it populates a persisted-computed-column, vs. adding a real column and running an UPDATE query to populate it? — NateJ

Stuart Ainsworth Stuart Ainsworth · Accepted Answer · 2012-06-21T17:40:57

In my experience, scalar udf's don't perform well on larger data sets; as a workaround you can try one of two options (and I'm not sure either of them will work particularly well):

Embed the logic of the function in the join itself, like so:

SELECT columnlist
FROM a JOIN b ON a.INT = (SELECT  CASE WHEN ( b.varchar= ''
                    OR b.varchar IS NULL
                    OR ISNUMERIC(b.varchar) = 0
                    OR b.varchar LIKE '%[^-+ 0-9]%'
                    OR b.varchar IN ( '.', '-', '+', '^' )
                  ) THEN NULL
             WHEN CAST(b.varchar AS NUMERIC(38, 0)) NOT BETWEEN -2147483648.
                                               AND         2147483647.
             THEN NULL
             ELSE CAST (b.varchar AS INT)
        END)

Change your user-defined function to be a inline table-valued function and use the CROSS APPLY syntax:

CREATE FUNCTION udf_ToInt
(   
    @str VARCHAR(MAX)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT  CASE WHEN ( @Str = ''
                    OR @Str IS NULL
                    OR ISNUMERIC(@Str) = 0
                    OR @Str LIKE '%[^-+ 0-9]%'
                    OR @Str IN ( '.', '-', '+', '^' )
                  ) THEN NULL
             WHEN CAST(@Str AS NUMERIC(38, 0)) NOT BETWEEN -2147483648.
                                               AND         2147483647.
             THEN NULL
             ELSE CAST (@Str AS INT) as IntVal
        END           

)
GO

SELECT columnlist
FROM b
CROSS APPLY udf_ToInt(b.varchar) t
JOIN a ON t.IntVal = a.Int

Probably easier to just convert to VARCHAR and compare :)

TSQL - A Better INT Conversion Function

1 Answers