0

How do I change column type from varchar to numeric which has data already in it, and the data is numbers.

SQL SERVER

Hamilton
  • 9
  • 2
  • 2
    You could add a new numeric column. Copy/convert data. Drop old column. Rename new column. But watch out for dependencies! (fk's, triggers, etc.) – jarlh Nov 13 '19 at 08:56

1 Answers1

0

Something like this,

  • 1, Create a new numeric column
  • 2, update new column with data from varchar column
  • 3, check and confirm the data in numeric column
  • 4, drop varchar column

But as mentioned in the comments you need to be aware of triggers.FKs etc Updated to use TRY_CONVERT

ALTER TABLE dbo.table ADD numeric_column(x,y); --x, y here is the precision, scale
GO

UPDATE dbo.table
SET    numeric_column = TRY_CONVERT(NUMERIC(x,y),varchar_numbers_column)
GO

ALTER TABLE DROP COLUMN varchar_numbers_column
GO

Using Try_Convert, returns NULL when conversion fails

SELECT
     Failed_Date        = TRY_CONVERT(NUMERIC(12,2), '12/31/2010')
    ,Failed_#           = TRY_CONVERT(NUMERIC(12,2), '#')
    ,WorkingWholeNumber = TRY_CONVERT(NUMERIC(12,2), '3')
    ,WorkingDecimal     = TRY_CONVERT(NUMERIC(12,2), '5.67')
Mazhar
  • 3,797
  • 1
  • 12
  • 29