mysql: how to truncate the length of a field

mysql field truncate alter
vaanipala picture vaanipala · Mar 8, 2012 · Viewed 19.6k times · Source 
Alter table merry_parents change mobile mobile char(10).

When I do the above I'm getting error:

#1265 - Data truncated for column 'mobile' at row 2

How can I truncate my mobile field to char(10)? Currently it is char(12).

Answer

Bojangles picture Bojangles · Mar 8, 2012

The error is telling you that there is data 12 characters long in row 2 (and probably others) so it's stopped the alter command to avoid losing data.

Try updating your table using SUBSTRING() to shorten the column. It's unclear why you want to do this as you'll lose data, but this will truncate the data to 10 characters long:

UPDATE merry_parents SET mobile=SUBSTRING(mobile, 1, 10)

Then run your alter command:

ALTER TABLE merry_parents CHANGE mobile mobile char(10).

Related questions

How to remove leading and trailing whitespace in a MySQL field?

I have a table with two fields (countries and ISO codes): Table1 field1 - e.g. 'Afghanistan' (without quotes) field2 - e.g. 'AF'(without quotes) In some rows the second field has whitespace at the start and/or end, …

Read More
Mysql Compare two datetime fields

I want to compare two dates with time, I want all the results from tbl where date1 > date2 Select * From temp where mydate > '2009-06-29 04:00:44'; but it is just comparing dates not time. it is giving …

Read More
IP address storing in MySQL database using PHP

what is the right field type for IP address in mysql? and what is the right way of storing it using PHP?

Read More