I need to be able to check if the first 4 characters in a varchar field are a
valid int. Is there anything similar to a NAN() function in TSQL that I could
use, or can anyone think of another way to do this?

Below is some sample code I was using to play with this. Obviously the NAN()
function won't work, but that's the idea.

DECLARE @string varchar(20),  @year int
SET @string = '2004 BMW 330xi'
SELECT @year = CASE WHEN NAN(LEFT(@string, 4))
        THEN NULL
        ELSE  CONVERT(int, LEFT(@string, 4)) END
PRINT @year

You can use the ISNUMERIC() function to test a string to see if it will
cast to a valid int.  Like this:

    DECLARE @string varchar(20),  @year int
    SET @string = '2004 BMW 330xi'
    SELECT @year = CASE WHEN ISNUMERIC(LEFT(@string, 4)) = 0
            THEN NULL
            ELSE  CAST(LEFT(@string, 4) AS INT) END
    PRINT @year


NB/ I also changed the CONVERT() to a CAST() because CAST() is part of
the ANSI standard but CONVERT() is Microsoft proprietary.  The only time
I use CONVERT() is when manipulating datetime values as different string
formats but I've learnt a few tricks with DATEADD() and DATEDIFF()
recently that make most of the things I used to do with CONVERT() redundant.

Show quoteHide quote

You can use ISNUMERIC() but also see
http://www.aspfaq.com/2390





Show quoteHide quote

How can I check if it is currently executing?
Left Joins (*=)
killed/rollback stuck on object_name(99)
Select if... endif
dynamic SQL error