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
>Solution :
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;