– Active: 1743054312921@@192.168.0.229@1433@SINO_SAP
– Active: 1753929430775@@192.168.3.1@3306@mysql
CREATE PROCEDURE ZY_P_CustomerPhone
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZY_TB_CustomerPhone]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[ZY_TB_CustomerPhone]( [CardCode] nvarchar(50) NOT NULL, [CardName] nvarchar(255) NULL, [Name] nvarchar(255) NULL, [Telephone] nvarchar(20) NOT NULL PRIMARY KEY ); END ELSE BEGIN TRUNCATE TABLE [dbo].[ZY_TB_CustomerPhone]; END; -- 组合所有客户电话数据 WITH CombinedResults AS ( -- OCPR SELECT o.cardcode AS CardCode, o.cardname AS CardName, a.Name, a.Telephone FROM OCRD o LEFT JOIN ( SELECT CardCode, Name, TRIM(tel1) AS Telephone FROM OCPR WHERE NULLIF(TRIM(tel1), '') IS NOT NULL UNION SELECT CardCode, Name, TRIM(tel2) AS Telephone FROM OCPR WHERE NULLIF(TRIM(tel2), '') IS NOT NULL ) a ON o.cardcode = a.cardcode UNION ALL -- ORDR SELECT o."CardCode" AS CardCode, o."CardName" AS CardName, o."U_consignee" AS Name, o."U_shtel" AS Telephone FROM SINO_SAP.dbo."ORDR" o LEFT JOIN SINO_SAP.dbo."RDR1" r ON o."DocEntry" = r."DocEntry" AND r."LineStatus" = 'o' WHERE o."CANCELED" = 'n' AND o."U_shtel" IS NOT NULL UNION ALL -- ODLN SELECT o."CardCode" AS CardCode, o."CardName" AS CardName, o."U_consignee" AS Name, o."U_shtel" AS Telephone FROM SINO_SAP.dbo."ODLN" o LEFT JOIN SINO_SAP.dbo."RDR1" r ON o."DocEntry" = r."DocEntry" AND r."LineStatus" = 'o' WHERE o."CANCELED" = 'n' AND o."U_shtel" IS NOT NULL ), SplitPhones AS ( SELECT CardCode, CardName, Name, CleanedPhone = CASE WHEN ProcessedPhone LIKE '1%' THEN LEFT(ProcessedPhone, 11) WHEN ProcessedPhone LIKE '01%' OR ProcessedPhone LIKE '02%' THEN LEFT(ProcessedPhone, 11) WHEN ProcessedPhone LIKE '03%' OR ProcessedPhone LIKE '04%' OR ProcessedPhone LIKE '05%' OR ProcessedPhone LIKE '06%' OR ProcessedPhone LIKE '07%' OR ProcessedPhone LIKE '08%' OR ProcessedPhone LIKE '09%' THEN LEFT(ProcessedPhone, 12) ELSE ProcessedPhone END FROM ( SELECT CardCode, CardName, Name, ProcessedPhone = CASE WHEN Cleansed LIKE '86%' THEN STUFF(Cleansed, 1, 2, '') WHEN Cleansed LIKE '00%' THEN '' ELSE Cleansed END FROM ( SELECT CardCode, CardName, Name, Cleansed = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( LTRIM(RTRIM(value)), ' ', ''), '-', ''), '(', ''), ')', ''), '.', ''), ',', ''), ';', ''), ':', ''), '+', '') FROM CombinedResults CROSS APPLY ( SELECT value FROM STRING_SPLIT( REPLACE(REPLACE(REPLACE(REPLACE( Telephone, ',', ';'), -- 处理逗号 ' ', ';'), -- 处理空格 '/', ';'), -- 处理斜杠 '、', ';'), -- 处理顿号 ';' ) WHERE LTRIM(RTRIM(value)) <> '' ) AS SplitValues WHERE Telephone IS NOT NULL AND Name IS NOT NULL -- 优化汉字检测逻辑 AND LTRIM(RTRIM(value)) NOT LIKE N'%[吖-座]%' AND LTRIM(RTRIM(value)) NOT LIKE N'%[一-鿕㐀-䶿豈-]%' ) AS FirstPass ) AS SecondPass WHERE ProcessedPhone <> '' ), ValidPhones AS ( SELECT CardCode, CardName, Name, CleanedPhone = CASE WHEN CleanedPhone LIKE '00%' AND LEN(CleanedPhone) > 4 THEN CleanedPhone WHEN CleanedPhone LIKE '0%' AND LEN(CleanedPhone) > 8 THEN CleanedPhone ELSE CleanedPhone END FROM SplitPhones WHERE LEN(CleanedPhone) BETWEEN 8 AND 20 AND CleanedPhone NOT LIKE '%[^0-9]%' AND TRY_CAST(CleanedPhone AS BIGINT) IS NOT NULL -- 防止整数溢出 ), RankedResults AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY Name, CleanedPhone ORDER BY CardCode, CardName ) AS rn FROM ValidPhones ) INSERT INTO [dbo].[ZY_TB_CustomerPhone] ( CardCode, CardName, Name, Telephone ) SELECT FinalRanked.CardCode, FinalRanked.CardName, Name, CleanedPhone AS Telephone FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY CleanedPhone ORDER BY CardCode, Name ) AS PhoneRank FROM RankedResults WHERE rn = 1 ) AS FinalRanked LEFT JOIN OCRD on FinalRanked.CardCode = OCRD.CardCode WHERE PhoneRank = 1 and OCRD.[frozenFor] ='N'; PRINT '成功更新';
END
修改代码 以 1 开头 → 截取前11位 满足正常手机号的格式
固话:以 01/ 02 开头 → 去除前3位以后,截取后续的8位
特殊号码:以 03- 09 开头 → 去除前4位以后,截取后续的8位