How To… Changing TEXT or NTEXT field to a VARCHAR field

As there is no direct way of converting a TEXT or NTEXT field to VARCHAR, it is necessary to rename your existing field, create a new field of the required size and then copy the existing data across, converting it, and then dropping the old column.
The following sample shows how to achieve this: –


sp_rename ‘table_name.original_field_name’, ‘temp_field_name’, ‘COLUMN’
ALTER TABLE [table_name] ADD [original_field_name] VARCHAR(1000)
UPDATE [table_name] SET [original_field_name] = SUBSTRING([temp_field_name], 1, 1000)
ALTER TABLE [table_name] DROP COLUMN [temp_field_name]


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s