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.

Answer 1

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):

1. 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)
   

2. 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 :)