how to change datatype of a field from varchar to int?

• dua_anshu

  SSC Veteran

  Points: 220

  More actions

  December 29, 2008 at 7:42 am

  #75387

  I have a field/column called A in table XX, initailly we decided to Varchar(50) as datatype for field/column A.

  Now we want to change the datatype of A to int.

  How can I do it? Also, field/column A does have data which is non-numeric.

  Please suggest.

  Thanks.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 29, 2008 at 7:48 am

  #918377

  rename the column.

  add a new column with the right name and data type.

  update the column to the value where the field was numeric.

  update the columns that were not numeric with some logicial value(zero?)

  exec sp_rename 'XX.A',,'A_old','column'

  ALTER TABLE XX Add A INT

  UPDATE XX SET A=A_old where Isnumeric(A_old)=1

  UPDATE XX SET A=0 WHERE Isnumeric(A_old)=0

  dua_anshu (12/29/2008)

  ---

  I have a field/column called A in table XX, initailly we decided to Varchar(50) as datatype for field/column A.

  Now we want to change the datatype of A to int.

  How can I do it? Also, field/column A does have data which is non-numeric.

  Please suggest.

  Thanks.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 29, 2008 at 7:43 pm

  #918721

  dua_anshu (12/29/2008)

  ---

  I have a field/column called A in table XX, initailly we decided to Varchar(50) as datatype for field/column A.

  Now we want to change the datatype of A to int.

  How can I do it? Also, field/column A does have data which is non-numeric.

  Please suggest.

  Thanks.

  What do you want to do with the non-numeric data?

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