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

Time:11-23

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

CodePudding user response:

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

  • Related