Working around ISNUMERIC

• David-250683

  Hall of Fame

  Points: 3452

  More actions

  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?

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  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)
  

• David-250683

  Hall of Fame

  Points: 3452

  More actions

  December 31, 2009 at 11:41 pm

  #1098064

  Thanks Jeff!!

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  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)