更新查询-T SQL

SNew 发表于 Dev

新品

SQL Server：2008 R2版本

我需要一个t-sql查询来从所有带有前缀代码的电话号码中删除国家/地区代码，例如香港的电话号码是8522345678901，我想删除852，结果应该是2345678901。列是国家/地区代码，例如HK，USA，UK，AUS，因此基于该国家/地区代码，我需要从PhoneNumbers表列（例如移动电话，传真，固定电话）中修剪前缀DialingPhoneCode。

电话号码的样本数据

Mobile     , Fax,        Landline,   CountryCode

61298765432, 228765432  , 598765432,  AUS
61298765432, 61228765432, 598765432,  AUS
85228157711, 28157711   , 85228157711,HK

电话代码的样本数据

DialingPhoneCode, CountryCode

61 ,             AUS
851,             HK

电话号码的预期输出

Mobile,Fax, Landline, CountryCode

298765432, 228765432, 598765432, AUS
298765432, 228765432, 598765432, AUS
28157711 , 28157711 , 28157711 , HK

Microsoft T-SQL

CREATE TABLE [dbo].[PhoneNumbers](
    [Mobile] [varchar](500) NULL,
    [Fax] [varchar](500) NULL,
    [Landline] [varchar](500) NULL,
    [CountryCode] [varchar](500)  NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[PhoneCodes](
    [DialingPhoneCode] [varchar](500) NULL,
    [CountryCode] [varchar](500)  NULL
) ON [PRIMARY]

GO

Gen Wan

您可以尝试以下方法：

SELECT 
    IIF(B.MOBILECODE = DialingPhoneCode, RIGHT(B.MOBILE, LEN(B.MOBILE)-B.CODELENGTH), B.MOBILE) AS UPDATEDMOBILE,
    IIF(B.FAXCODE = DialingPhoneCode, RIGHT(B.FAX, LEN(B.FAX)-B.CODELENGTH), B.FAX) AS UPDATEDFAX,
    IIF(B.LANDLINECODE = DialingPhoneCode, RIGHT(B.LANDLINE, LEN(B.LANDLINE)-B.CODELENGTH), B.LANDLINE) AS UPDATEDLANDLINE,
    B.COUNTRYCODE
FROM (
SELECT A.MOBILE, A.FAX, A.LANDLINE, A.COUNTRYCODE, A.DialingPhoneCode,A.CODELENGTH
,LEFT(A.MOBILE, A.CODELENGTH) AS MOBILECODE
,LEFT(A.Fax, A.CODELENGTH) AS FAXCODE
,LEFT(A.Landline, A.CODELENGTH) AS LANDLINECODE
FROM (
SELECT P1.Mobile,P1.Fax,P1.Landline,P1.CountryCode, P2.DialingPhoneCode
, LEN(P2.DialingPhoneCode) AS CODELENGTH
FROM PhoneNumbers P1
LEFT JOIN PhoneCodes P2
ON P1.CountryCode = P2.CountryCode)A )B

IIF函数只能在SQL Server 2012或更高版本中使用。

如果使用的是旧版本，则可以使用case语句替换它们。

测试结果（我又添加了两个测试样本）：

DB <>提琴

----更新----

添加更新和案例语句：

UPDATE PhoneNumbers
SET Mobile = C.UPDATEDMOBILE,
    Fax = C.UPDATEDFAX,
    Landline = C.UPDATEDLANDLINE
FROM
(
SELECT 
CASE WHEN B.Mobile IS NULL THEN NULL
     WHEN B.MOBILE IS NOT NULL AND B.MOBILECODE = DialingPhoneCode THEN RIGHT(B.MOBILE, LEN(B.MOBILE)-B.CODELENGTH) 
     ELSE B.MOBILE END AS UPDATEDMOBILE,
CASE WHEN B.Fax IS NULL THEN NULL
     WHEN B.FAXCODE = DialingPhoneCode THEN RIGHT(B.FAX, LEN(B.FAX)-B.CODELENGTH) 
     ELSE B.FAX END AS UPDATEDFAX,
CASE WHEN B.Landline IS NULL THEN NULL
     WHEN B.LANDLINECODE = DialingPhoneCode THEN RIGHT(B.LANDLINE, LEN(B.LANDLINE)-B.CODELENGTH) 
     ELSE B.LANDLINE END AS UPDATEDLANDLINE,
    B.COUNTRYCODE, B.Mobile, B.Fax, B.Landline
FROM (
SELECT A.MOBILE, A.FAX, A.LANDLINE, A.COUNTRYCODE, A.DialingPhoneCode,A.CODELENGTH
,LEFT(A.MOBILE, A.CODELENGTH) AS MOBILECODE
,LEFT(A.Fax, A.CODELENGTH) AS FAXCODE
,LEFT(A.Landline, A.CODELENGTH) AS LANDLINECODE
FROM (
SELECT P1.Mobile,P1.Fax,P1.Landline,P1.CountryCode, P2.DialingPhoneCode
, LEN(P2.DialingPhoneCode) AS CODELENGTH
FROM PhoneNumbers P1
LEFT JOIN PhoneCodes P2
ON P1.CountryCode = P2.CountryCode)A )B )C
WHERE   C.Mobile = PhoneNumbers.Mobile 
    AND C.Fax = PhoneNumbers.Fax
    AND C.Landline = PhoneNumbers.Landline

测试结果：

DB <>提琴

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。