如何在Oracle SQL插入语句中去除phone字段的连字符与空格？

Here's the adjusted SQL statement that removes all hyphens and spaces from the cb.phone value, leaving only digits before inserting it into the DWCUST table:

INSERT INTO DWCUST (
    DWCUSTID, 
    DWSOURCEIDBRIS, 
    DWSOURCEIDMELB, 
    FIRSTNAME, 
    SURNAME, 
    GENDER, 
    PHONE, 
    POSTCODE, 
    CITY, 
    STATE, 
    CUSTCATNAME
) 
SELECT 
    dwcustSeq.nextval, 
    cb.custid, 
    Null, 
    cb.fname, 
    cb.sname, 
    UPPER(cb.gender), 
    -- Strip all non-digit characters (hyphens, spaces, etc.) from phone number
    REGEXP_REPLACE(cb.phone, '[^0-9]', ''), 
    cb.postcode, 
    cb.city, 
    cb.state, 
    cc.custcatname 
FROM a2custbris cb 
NATURAL JOIN a2custcategory cc 
WHERE cb.rowid IN (
    SELECT source_rowid FROM A2ERROREVENT where filterid = 5
);

Quick Breakdown: #

• The REGEXP_REPLACE function targets any character that's not a digit (matched by the regex pattern [^0-9]) and replaces it with an empty string. This cleans up hyphens, spaces, or any other non-numeric characters from the phone number string in one go.
• Since cb.phone is a VARCHAR2 type, this regex operation works smoothly without type conversion issues, ensuring only numeric characters end up in the PHONE column of DWCUST.

内容的提问来源于stack exchange，提问作者Wah.P