How to check if the column is int?

• iceheartjade

  Say Hey Kid

  Points: 677

  More actions

  February 1, 2008 at 2:54 pm

  #120560

  Hi, All

  What's the easist way to check if a varchar column stores a number?

  Thanks

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  February 1, 2008 at 2:59 pm

  #774477

  Take a look at the IsNumeric() function. That should get you started.

• iceheartjade

  Say Hey Kid

  Points: 677

  More actions

  February 1, 2008 at 3:03 pm

  #774480

  Great, thanks

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  February 1, 2008 at 3:39 pm

  #774492

  -- Convert to int if a valid integer

  select

  TestData,

  Output_INT =

  case

  when TestData like '-%[^0-9]%'then null

  when TestData not like '-%' and

  TestData like '%[^0-9]%' then null

  whenTestData not like '-%' and

  datalength(TestData) > 10then null

  whenTestData like '-%' and

  datalength(TestData) > 11then null

  whenconvert(bigint,TestData) between

  -2147483648 and 2147483647then convert(int,TestData)

  else null

  end

  from

  (

  -- Test data

  Select TestData = '1234a'union all

  Select TestData = '1234.'union all

  Select TestData = '12340'union all

  Select TestData = '-12340'union all

  Select TestData = '1234E'union all

  Select TestData = '1234'union all

  Select TestData = '-92233720368547758081'union all

  Select TestData = '9223372036854775808'union all

  Select TestData = '-2147483649'union all

  Select TestData = '-2147483648'union all

  Select TestData = '2147483647'union all

  Select TestData = '2147483648'

  ) a

  Results:

  TestData Output_INT

  --------------------- -----------

  1234a NULL

  1234. NULL

  12340 12340

  -12340 -12340

  1234E NULL

  1234 1234

  -92233720368547758081 NULL

  9223372036854775808 NULL

  -2147483649 NULL

  -2147483648 -2147483648

  2147483647 2147483647

  2147483648 NULL

  (12 row(s) affected)

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  February 1, 2008 at 5:55 pm

  #774502

  Pam Brisjar (2/1/2008)

  ---

  Take a look at the IsNumeric() function. That should get you started.

  Be careful, folks... IsNumeric allows for things like currency symbols, commas, periods, dashes, plus signs, and special scientific notation like 10E2 and 10D2. IsNumeric should never be used as an IsAllDigits function because it just doesn't work that way. You WILL need to use some LIKEs as Michael did above...

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