MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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');

Records: 7  Duplicates: 0  Warnings: 0

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

new_size

3

3

4

19

null

null

19

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;

Records: 0  Duplicates: 0  Warnings: 0

Rows matched: 7  Changed: 5  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

Query was empty

select * from bengaluru_house_prices;

new_size

3

3

4

19

null

null

19

new_size
3
3
4
19
null
null
19

new_size
3
3
4
19
null
null
19