wants to add an integer value to a column which is char type.

• MuditGupta

  SSCrazy

  Points: 2596

  More actions

  October 17, 2008 at 8:43 am

  #383138

  Hi

  I have a column with datatype char(2). I want to add a fixed number say 7 to each of its record and store it as new column. Something like this:

  Col_original Col_new

  007

  018

  029

  0310

  etc

  something like.. i want to perform arithmetic operation on char column which in this case is addition.

  I think i should be able to convert 'Col_original' to int type AS

  Col_original

  0

  1

  2

  3

  Then , addition should be easy.

  Please suggest,

  Thanks

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 17, 2008 at 8:50 am

  #886414

  What issues are you running into in your attempt that you need help with?

  declare @C char(2),

  @d char(2)

  set @C = '01'

  set @d = '10'

  select @C + 7, @D + 7

  Results:

  8 | 17

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  October 17, 2008 at 8:50 am

  #886415

  Yes you can do what you are planning and pretty simply as long as all the character values CAN be converted to integer. I'd do it something like this:

  Update table

  Set int_column = Convert(int, char_column) + 7

  Where

  -- only do it to covertable columns

  IsNumeric(char_column) = 1

  Then I can look for Nulls in the int_column which will help me find and fix inaccurate data in the char_column. The only issue with IsNumeric is that is considers '.', '+', '-', and currency symbols to be numeric so you could still get some errors.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question