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

Posted on by
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: –

EXEC

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

 
 

Leave a Reply

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

Gravatar
WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Cancel

Connecting to %s