Clean Values Before Altering The Column Data Types SQL

Posted: Monday, September 9, 2024

Here's a step-by-step approach to handle this issue:

Data Types SQL

1. Identify Non-Integer Values

First, find all the non-integer values in the columns:

SELECT `id`, `city` FROM `tb_users` WHERE `city` NOT REGEXP '^[0-9]*$';
SELECT `id`, `state` FROM `tb_users` WHERE `state` NOT REGEXP '^[0-9]*$';
SELECT `id`, `region` FROM `tb_users` WHERE `region` NOT REGEXP '^[0-9]*$';
SELECT `id`, `country` FROM `tb_users` WHERE `country` NOT REGEXP '^[0-9]*$';

2. Clean Non-Integer Values

You need to decide what to do with these non-integer values. Common options include setting them to NULL, a default value like 0, or removing those rows if they’re not needed.

Option A: Set Non-Integer Values to NULL

UPDATE `tb_users` 
SET `city` = NULL 
WHERE `city` NOT REGEXP '^[0-9]*$';

UPDATE `tb_users` 
SET `state` = NULL 
WHERE `state` NOT REGEXP '^[0-9]*$';

UPDATE `tb_users` 
SET `region` = NULL 
WHERE `region` NOT REGEXP '^[0-9]*$';

UPDATE `tb_users` 
SET `country` = NULL 
WHERE `country` NOT REGEXP '^[0-9]*$';

Option B: Set Non-Integer Values to a Default Value

UPDATE `tb_users` 
SET `city` = 0 
WHERE `city` NOT REGEXP '^[0-9]*$';

UPDATE `tb_users` 
SET `state` = 0 
WHERE `state` NOT REGEXP '^[0-9]*$';

UPDATE `tb_users` 
SET `region` = 0 
WHERE `region` NOT REGEXP '^[0-9]*$';

UPDATE `tb_users` 
SET `country` = 0 
WHERE `country` NOT REGEXP '^[0-9]*$';

Option C: Remove Rows with Non-Integer Values (Use Caution)

If you decide to remove rows with non-integer values, be very careful, as this will permanently delete data:

DELETE FROM `tb_users` 
WHERE `city` NOT REGEXP '^[0-9]*$';

DELETE FROM `tb_users` 
WHERE `state` NOT REGEXP '^[0-9]*$';

DELETE FROM `tb_users` 
WHERE `region` NOT REGEXP '^[0-9]*$';

DELETE FROM `tb_users` 
WHERE `country` NOT REGEXP '^[0-9]*$';

3. Alter the Table Structure

After cleaning the data, try running the ALTER TABLE command again:

ALTER TABLE `tb_users`
MODIFY COLUMN `country` INT,
MODIFY COLUMN `region` INT,
MODIFY COLUMN `state` INT,
MODIFY COLUMN `city` INT;

This process will ensure that the columns can be safely converted to INT without encountering

Download ANSNEW APP For Ads Free Experiences!

✖

Yamin Hossain Shohan

Software Engineer, Researcher & Digital Creator

I'm a researcher, software engineer, and digital creator who uses technology and creativity to make useful tools and create interesting content.

Copyright Disclaimer

✖

All the information is published in good faith and for general information purpose only. We does not make any warranties about the completeness, reliability and accuracy of this information. Any action you take upon the information you find on ansnew.com is strictly at your own risk. We will not be liable for any losses and/or damages in connection with the use of our website. Please read our complete disclaimer. And we do not hold any copyright over the article multimedia materials. All credit goes to the respective owner/creator of the pictures, audios and videos. We also accept no liability for any links to other URLs which appear on our website. If you are a copyright owner or an agent thereof, and you believe that any material available on our services infringes your copyrights, then you may submit a written copyright infringement notification using the contact details