Search code examples
sqlsql-servercastingintegervarchar

Trouble converting int to varchar in update

I'm having trouble casting an int to varchar in an UPDATE.

field1 is an int result from a calculated column. I need it to be varcahr as I want 'Y' instead of '1'.

UPDATE table1 
SET field1 = CAST(field1 AS VARCHAR(1))
UPDATE table1 
SET field1 = CASE WHEN field1 = '1' THEN 'Y' ELSE 'N' END

Conversion failed when converting the varchar value 'Y' to data type int.

Solution

  • If you look at the column type of field1 you'll see SQL Server is storing it as an int

    If you want to store a varchar value you'll have to add a new varchar column to the table

    ALTER TABLE table1 ADD vfield varchar(1)

    Then update the newly added text column

    UPDATE table 1 SET vfield = CASE WHEN field1 = 1 THEN 'Y' ELSE 'N' END

    This is causing redundancy in your table and you should consider using a view or looking at how the output is being formatted.

    It would be more efficient to store a Y/N value as a bit type column (1,0) and format it dynamically as 'Yes' or 'No' in your visualisation tool