Answers.Body: <p>You have all the control of your custom table but always it is not advised to change custom field type in the database </p>
  
  <p>Better way of doing this would be</p>
  
  <p>In DB - Create a temp table of the same and move all the records to the temp table first</p>
  
  <p>In the front end - delete the column with varchar and create a new one with int with the same name </p>
  
  <p>In DB - move all the value from temp table to the custom field </p>
  
  <p>I guess this helps !!!</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-05-26T10:09:49.473Z
Answers.Text: Thank you for your reply Ramesh, but what about the type bigint ? Is it understandable as the type int in CiviCRM ?
Answers.UserDisplayName: VangelisP
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-05-26T10:59:30.323Z
Answers.Text: I guess - you need change in the DB level from int to big int - manually
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-05-26T08:52:16.917Z
Answers.LastActivityDate: 2016-05-26T08:52:16.917Z
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.Body: <p>I have similiar, though not exact, situation... my issue is VarChar field type with field values;</p>
  
  <p>Field_name=estimated_household_income_56 
  Field_type=varchar(60) </p>
  
  <p>As result of "varchar" field type, the field values do not enable "correct" indexing or "range" searching.  In fact, the Ascending Index of potential values results as follows; </p>
  
  <p>$1,000,000
  $1,000,001
  $100,000
  $150,000
  $200,000
  $25,000
  $250,000
  $50,000
  $500,000
  $75,000</p>
  
  <p>(these field values were already modified by me, '$0 to $24,999' SET to '$25,000' - using SQL UPDATE/SET/WHERE regime)</p>
  
  <p>I know this will be cleaned up by changing to INT field type.  IS it as simple as simply changing field type?  OR, should I first, execute SQL commands to modify field values ELIMINATING the '$' and ',' from the field values, then change to INT.</p>
  
  <p>My gut says get rid of the '$' and ',', but hoping this is a single click operation.  Of course, did an SQL export of the table as backup.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-06T18:38:12.813Z
Answers.Text: This is possibly better posted as a separate question, instead of an answer on another question but you will need to alter the data so it's all integers (removing comma's and dollar signs), otherwise mysql will issue a warning when you try and change the data type.
Answers.UserDisplayName: Luke Stewart
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-02T20:46:19.997Z
Answers.LastActivityDate: 2019-02-02T20:53:04.650Z
Answers.LastEditDate: 2019-02-02T20:53:04.650Z
Answers.UserDisplayName: drummondislebsd
Body: <p>I have a custom group where one of its custom fields is of type varchar(64). This field is storing huge numbers only (something that I did not know during development) so I want to convert it into a int type, a bigint type due to the really lengthy numbers it's storing
  My question is the following: 
  Assuming that the length is the same in all stored entries (around 2,5 million entries, 12 or 16 digit length), can I convert it into another type simply by ALTER TABLE or I need to inform Civi that this is an int/bigint field? Lastly, since I haven't seen the use of Bigint, is it safe to assume that it won't cause havoc in Civi's functionality if I do so?</p>
  
  <p>Thanks!</p>
  
Tags: <custom-fields><mysql>
Title: Change of custom field type to bigint
ThreadText: # Change of custom field type to bigint
  
  (Asked by: username_0 on 2016-05-26)
  
  I have a custom group where one of its custom fields is of type varchar(64). This field is storing huge numbers only (something that I did not know during development) so I want to convert it into a int type, a bigint type due to the really lengthy numbers it's storing
  My question is the following:
  Assuming that the length is the same in all stored entries (around 2,5 million entries, 12 or 16 digit length), can I convert it into another type simply by ALTER TABLE or I need to inform Civi that this is an int/bigint field? Lastly, since I haven't seen the use of Bigint, is it safe to assume that it won't cause havoc in Civi's functionality if I do so?
  
  Thanks!
  
  **Tags:** custom-fields, mysql
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2016-05-26. Score: 1 | Accepted answer
  
  You have all the control of your custom table but always it is not advised to change custom field type in the database
  
  Better way of doing this would be
  
  In DB - Create a temp table of the same and move all the records to the temp table first
  
  In the front end - delete the column with varchar and create a new one with int with the same name
  
  In DB - move all the value from temp table to the custom field
  
  I guess this helps !!!
  
  > **Comments:**
  > **username_0**: Thank you for your reply Ramesh, but what about the type bigint ? Is it understandable as the type int in CiviCRM ?  
  > **username_1**: I guess - you need change in the DB level from int to big int - manually  
  
  ---
  
  ### Answer by username_2 on 2019-02-02. Score: 0
  
  I have similiar, though not exact, situation... my issue is VarChar field type with field values;
  
  Field\_name=estimated\_household\_income\_56
  Field\_type=varchar(60)
  
  As result of "varchar" field type, the field values do not enable "correct" indexing or "range" searching. In fact, the Ascending Index of potential values results as follows;
  
  $1,000,000
  $1,000,001
  $100,000
  $150,000
  $200,000
  $25,000
  $250,000
  $50,000
  $500,000
  $75,000
  
  (these field values were already modified by me, '$0 to $24,999' SET to '$25,000' - using SQL UPDATE/SET/WHERE regime)
  
  I know this will be cleaned up by changing to INT field type. IS it as simple as simply changing field type? OR, should I first, execute SQL commands to modify field values ELIMINATING the '$' and ',' from the field values, then change to INT.
  
  My gut says get rid of the '$' and ',', but hoping this is a single click operation. Of course, did an SQL export of the table as backup.
  
  > **Comments:**
  > **username_3**: This is possibly better posted as a separate question, instead of an answer on another question but you will need to alter the data so it's all integers (removing comma's and dollar signs), otherwise mysql will issue a warning when you try and change the data type.  
  
  ---
  
  