How to split if number found and insert into new column mysql?

user_14175153 0
Viewed 25

How to split if number is found in mysql? here @FaNo_FN give me solution to split number. But I'm not able to insert into the datas to new row

"Cyclimorph 10 Injection" "Symbicort Turbohaler 200 Inhalation powder"

I want to insert "Cyclimorph" & "Symbicort Turbohaler" one column and "10 Injection","200 Inhalation powder"

CREATE TABLE mytable (val varchar(200));

INSERT INTO mytable VALUES ("Cyclimorph 10 Injection"),
("Symbicort Turbohaler 200 Inhalation powder");

SELECT SUBSTRING(Val,1,LOCATE("~",val2)-1), 
       SUBSTRING(Val,LOCATE("~",val2)) FROM
(SELECT val, REGEXP_REPLACE(Val,"[0-9]","~") val2 
   FROM mytable) B

this query can split data but i'm not able to insert in new column by separating data. this is my query

insert into tbl_drug_uk_2 (brand) VALUES (
       SELECT SUBSTRING(brand,1,LOCATE("~",val2)-1) FROM ( SELECT brand, REGEXP_REPLACE(brand,"[0-9]","~") val2 FROM tbl_drug_uk) A
    )

I'm alo try anothers query but failed. please give me suggestion

mysql
1 Answers

On MySQL 8+, we can use a combination of REGEXP_SUBSTR and REGEXP_REPLACE:

SELECT
    REGEXP_REPLACE(val, '\\s+\\d+.*$', '') AS part1,
    REGEXP_SUBSTR(val, '\\d+.*$') AS part2
FROM mytable;

Demo

user_18632290
Related