A column contains both CHAR and INTEGER

• onlo

  SSCertifiable

  Points: 5080

  More actions

  May 19, 2010 at 2:41 am

  #221262

  Hello, I have a column contains both CHAR and INTEGER.

  I need to write a stored procedure to handle them differently :

  IF the column contains CHAR, eg. 'A01', 'B02', 'C03'

  Display it as : 'A01'

  IF the column contains integer numbers (but their data types are still CHAR, of course)

  Display it as : integer number x 3

  How should I write this program ?

• Ken McKelvey

  SSCoach

  Points: 18765

  More actions

  May 19, 2010 at 3:15 am

  #1169293

  -- *** Test Data ***

  DECLARE @t TABLE

  (

  YCol varchar(10) NOT NULL

  )

  INSERT INTO @t

  SELECT 'A01'

  UNION ALL SELECT 'B02'

  UNION ALL SELECT 'C03'

  UNION ALL SELECT '2'

  -- *** End Test Data ***

  SELECT

  CASE

  WHEN YCol LIKE '%[^0-9]%'

  THEN YCol

  ELSE CAST(YCol * 3 AS varchar(10))

  END AS YCol

  FROM @t

• onlo

  SSCertifiable

  Points: 5080

  More actions

  May 19, 2010 at 4:30 am

  #1169312

  Thanks

• kalyan.nimbala

  Mr or Mrs. 500

  Points: 524

  More actions

  May 26, 2010 at 12:42 pm

  #1172968

  isnumeric(column name) to check whether the column has any numeric values in it.

• ColdCoffee

  SSC-Dedicated

  Points: 39971

  More actions

  May 26, 2010 at 8:18 pm

  #1173090

  Kalyan, no, ISNUMERIC cannot be used as a detector to check if a column contains only numerics

  Try this:

  DECLARE @t TABLE

  (

  YCol varchar(10) NOT NULL

  )

  INSERT INTO @t

  SELECT 'A01'

  UNION ALL SELECT 'B02'

  UNION ALL SELECT 'C03'

  UNION ALL SELECT '2'

  UNION ALL SELECT '1,' -- this will be a good numeral according to ISNUMERIC, but is it really?

  UNION ALL SELECT CHAR(10) -- this is line feed

  SELECT YCol FROM @t

  WHERE ISNUMERIC(YCol) = 1