Change Field Type with data to Text

Sometimes, our client can change his/her requirements and that change might involve changing the type of a field from any type to TEXT. Now, Drupal 7 does not support changing the field types if there is existing data.

First thing which comes to our mind is to create a new field and delete the old one, but we have data in the fields and we cannot afford to lose them. So, what to do now?

There might be many ways to do this, but the thing which I found satisfactory and used it, is to change the settings of the field in the drupal installation's database. But before doing this, do keep a dump of your database so that you can revert back to status quo. And do check your queries not twice but three or four times before running them since you will be changing the database of your drupal installation.

For this you have to run around 8 queries

Query 1 and 2: -

Change the type of column in the field's tables.

ALTER TABLE field_data_FIELD_NAME MODIFY FIELD_NAME_value varchar(SIZE);

ALTER TABLE field_revision_FIELD_NAME MODIFY FIELD_NAME_value varchar(SIZE);

Here, SIZE can be 10, 20, 255 etc.

FIELD_NAME is the machine of the field.

Query 3 & 4: -

Add the FIELD_NAME_format column in the field's tables. (Run these two queries only if the columns does not exist.)

ALTER TABLE field_data_FIELD_NAME ADD FIELD_NAME_format VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

ALTER TABLE field_revision_FIELD_NAME ADD FIELD_NAME_format VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

Query 5 & 6: -

Update type and module column of field_config table. 

UPDATE field_config SET type = 'text' WHERE field_name = 'FIELD_NAME';

UPDATE field_config SET module = 'text' WHERE field_name = 'FIELD_NAME';

Query 7: -

Update data column of field_config_instance table. In this since we will be working with data in serialized form, you have to make sure that you are attentive to minute details.

UPDATE field_config_instance SET data = 'a:7:{s:5:"label";s:LENGTH_OF_LABEL:"LABEL OF FIELD";s:6:"widget";a:5:{s:6:"weight";s:1:"2";s:4:"type";s:14:"text_textfield";s:6:"module";s:4:"text";s:6:"active";i:1;s:8:"settings";a:1:{s:4:"size";s:2:"SIZE";}}s:8:"settings";a:2:{s:15:"text_processing";s:1:"0";s:18:"user_register_form";b:0;}s:7:"display";a:1:{s:7:"default";a:5:{s:5:"label";s:5:"above";s:4:"type";s:12:"text_default";s:8:"settings";a:0:{}s:6:"module";s:4:"text";s:6:"weight";i:WEIGHT;}}s:8:"required";i:REQUIRED;s:11:"description";s:LENGTH_OF_THE_FOLLOWING_STRING:"DESCRIPTION";s:13:"default_value";N;}' WHERE field_name = 'FIELD_NAME';

REQUIRED = 0 or 1.

WEIGHT = Weight of the field in content type.

DESCRIPTION = Description, if any.

In the table field_config_instance, there is one more column FIELD_ID, keep in mind the value in this field for field_name = 'FIELD_NAME'.

Query 8: -

Update the data column of field_config table.

UPDATE field_config SET data = 'a:7:{s:12:"translatable";s:1:"0";s:12:"entity_types";a:0:{}s:8:"settings";a:1:{s:10:"max_length";s:2:"MAX_LENGTH_OF_FIELD";}s:7:"storage";a:5:{s:4:"type";s:17:"field_sql_storage";s:8:"settings";a:0:{}s:6:"module";s:17:"field_sql_storage";s:6:"active";s:1:"1";s:7:"details";a:1:{s:3:"sql";a:2:{s:18:"FIELD_LOAD_CURRENT";a:1:{s:LENGTH_OF_THE_FOLLOWING_STRING:"field_data_FIELD_NAME";a:2:{s:LENGTH_OF_THE_FOLLOWING_STRING:"value";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_value";s:6:"format";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_format";}}s:19:"FIELD_LOAD_REVISION";a:1:{s:LENGTH_OF_THE_FOLLOWING_STRING:"field_revision_FIELD_NAME";a:2:{s:5:"value";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_value";s:6:"format";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_format";}}}}}s:12:"foreign keys";a:1:{s:6:"format";a:2:{s:5:"table";s:13:"filter_format";s:7:"columns";a:1:{s:6:"format";s:6:"format";}}}s:7:"indexes";a:1:{s:6:"format";a:1:{i:0;s:6:"format";}}s:2:"id";s:4:"FIELD_ID";}' WHERE field_name = 'FIELD_NAME';

 After running these queries the type of field will be changed from any other type to TEXT.

If you are not sure that changing the data values would work, then the best way is to create the field in any other drupal instance, copy the data from there, and run the queries using that data. But do keep in mind to change the FIELD_ID.