Change field type with data to text
2013 10 Dec

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.

Latest Blogs

Drupal 9 Upgrade

The Roadmap to upgrade Drupal site to Drupal 9

Drupal is approaching with its next big evolution and the upcoming release that is none other than Drupal 9.

Read More

How to Choose Web Development Agencies

Points to check before choosing the right website development company

In today’s day and age, where the digital population is increasing exponentially, no credible business can or should refrain from having a strong and steady web presence.

Read More

Wordpress to Drupal

Should I plan to move my Wordpress Website into the Latest Drupal version?

If you're here, I assume you already have a website built on WordPress but you're not satisfied with it, or perhaps you're here to have a clear idea about why you should give a thought to migrating

Read More

Drupal 9 Easy Upgrade

Why is Drupal 9 going to be an easy upgrade compared to the previous major versions?

Lately, there has been a lot of buzz around the corner about Drupal 9. The Drupal community is launching Drupal 9, and as well as have plans for the EOL of Drupal 7 and Drupal 8 by 2021.

Read More