Post reply

Working around ISNUMERIC

  • December 31, 2009 at 2:30 pm

    #217930

    Since ISNUMERIC will return 1 for "some characters that are not numbers" I will usually accept values passed to a stored procedure as a string. I then usually see whether the string is LIKE a number. Everything works fine until I use a variable character data type. For example:

    DECLARE @Preview char(1) = ''

    SET @Preview = CASE WHEN @preview NOT LIKE N'%[^0-2]%' THEN CAST(@preview AS tinyint) ELSE NULL END

    SELECT @Preview as 'char(1)'

    GO

    DECLARE @Preview varchar(1) = ''

    SET @Preview = CASE WHEN @preview NOT LIKE N'%[^0-2]%' THEN CAST(@preview AS tinyint) ELSE NULL END

    SELECT @Preview as 'varchar(1)'

    GO

    DECLARE @Preview char(50) = ''

    SET @Preview = CASE WHEN @preview NOT LIKE N'%[^0-2]%' THEN CAST(@preview AS tinyint) ELSE NULL END

    SELECT @Preview as 'char(3)'

    GO

    Why does the second select return 0, while the others return the expected NULL?

  • December 31, 2009 at 8:55 pm

    #1098044

    Because, as a default, VARCHAR is capable of holding an empty string while CHAR is blank padded. See ANSI_PADDING in Books Online. If you SET ANSI_PADDING OFF, the rules change.

    DECLARE @Preview CHAR(1)

    set @Preview = ''

    select '|'+@Preview+'|'

    GO

    DECLARE @Preview VARCHAR(50)

    set @Preview = ''

    select '|'+@Preview+'|'

    GO

    DECLARE @Preview CHAR(50)

    set @Preview = ''

    select '|'+@Preview+'|'

    GO

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 31, 2009 at 11:41 pm

    #1098064

    Thanks Jeff!!

  • January 1, 2010 at 1:27 am

    #1098065

    You bet. Thanks for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply