How to use REGEXP in INSERT INTO SELECT statement to copy number values only before insert?

Question

I have insert into select statement to copy value from one table to another.

There's am mobile column from both tables but in the source table the mobile number has a text attached to it like this : Primary contact : 1234567890.

So I'd like to copy only the 1234567890 part of the mobile.

I've done this using subquery with REGEXP:

INSERT INTO employee (employee_number, mobile) 

SELECT eb.employee_number,(SELECT mobile FROM employee_bulkadd_staging WHERE mobile REGEXP '^[0-9]+$')

FROM employee_bulkadd_staging eb

But it inserts NULL value to my mobile column.

I've also done this:

   INSERT INTO employee (employee_number, mobile) 

    SELECT eb.employee_number,eb.mobile REGEXP '^[0-9]+$'

    FROM employee_bulkadd_staging eb

But that inserts 0 as value.

Please let me know what is the best way to do this. Or what could I be doing wrong using REGEXP? Thanks.

Answer 1

You can do INSTR() and SUBSTR()

SELECT SUBSTRING( mobile FROM INSTR(mobile, ':')+1) 
FROM employee_bulkadd_staging

Answer 2

You can use:

select REGEXP_SUBSTR(col1,"[0-9]+") as col1 from test;

Test example:

create table test (
col1 varchar(255));

insert into test values
('Primary contact : 1234567890.'),
('1');

Result:

amount
1234567890
1

In your case :

INSERT INTO employee ( employee_number, 
                       mobile
                      ) 
SELECT eb.employee_number,
       ( SELECT mobile 
         FROM employee_bulkadd_staging 
         WHERE mobile  REGEXP_SUBSTR(col1,"[0-9]+")
         )

FROM employee_bulkadd_staging eb

Answer 3

did you try using REPLACE

INSERT INTO employee (employee_number, mobile) 

SELECT eb.employee_number,REPLACE(eb.mobile,'Primary contact : ','')

FROM employee_bulkadd_staging eb