SQL Server Trouble converting int to varchar in update [closed]

kkih6yb8  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(107)

Closed. This question is not reproducible or was caused by typos . It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed 5 days ago.
Improve this question

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.

bvjveswy

bvjveswy1#

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

相关问题