How To split and update column at the same time in Mysql?

Question

I'm working on a mini Project for my portfolio. Which is done except there is one column name size which contain integer space and alphabets. For example

size    

3 ABC

4 XYZ

19 pqr

I want to update this table by removing the alphabet, So that it looks like this

size

3

4

19

I have tried different methods, which basically gives me syntax error.

alter table bengaluru_house_prices
modify column size substring_index(size, ' ' , 1);



alter table bengaluru_house_prices
modify column size integer;

Thank you

Does this answer your question? [Cast from VARCHAR to INT - MySQL](https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql) — Martin, Nov 22 '22 at 13:36
Also your SQL is wrong; if you want to update the column definition in MySQL you should be doing: `ALTER TABLE CHANGE INT(10) NOT NULL` . The term "modify" doesn't exist in this context. — Martin, Nov 22 '22 at 13:43

score 1 · Accepted Answer · answered Nov 22 '22 at 13:42

If you like only the starting integers you could use REGEXP_SUBSTR.

Consider the following data.

CREATE TABLE bengaluru_house_prices (
 size varchar(20) );

insert into bengaluru_house_prices values ('3 ABC'),('3 ABC 4'),('4 XYZ'),('19 pqr'),('.'),('.'),('19 pqr25');

Select,

select REGEXP_SUBSTR(size,"[0-9]+") as new_size 
from bengaluru_house_prices;

Result:

new_size
3
3
4
19
null
null
19

To update the table, I suggest create another column then update it and drop it in the end

SET autocommit=0;
LOCK TABLES bengaluru_house_prices WRITE;
alter table bengaluru_house_prices add column new_size int default null;
update bengaluru_house_prices 
set new_size = REGEXP_SUBSTR(size,"[0-9]+") ;
alter table bengaluru_house_prices drop column size ;
COMMIT;
UNLOCK TABLES;

https://dbfiddle.uk/2oMqufmi

Regex is a really expensive way of doing this sort of transition. — Martin, Nov 22 '22 at 13:46

How To split and update column at the same time in Mysql?

1 Answers1