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

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.

CodePudding user response：

You can do INSTR() and SUBSTR()

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

CodePudding user response：

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

CodePudding user response：

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