主页 / dba / 问题 / 138373
Accepted
Geoff Dawdy
Asked: 2016-05-13 08:49:05 +0800 CST

合并地址缺少单元/套房#

  • 772

我有一个需要在 SQL Server 中更新的地址列表。我怎样才能将 Suite # 添加到缺少它的地址？

Address
1 Burton Hills Blvd, Suite 250
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Burton Hills Blvd
1 Ford Pl
1 Ford Pl, Suite 5B
1 Kish Dr
1 Park Center Dr, Suite 11
1 Park Center Dr
sql-server sql-server-2012

1 个回答

  1. Best Answer

    这不是一个干净的问题。假设不会有多个具有不同套房号的街道地址，以下内容应该可以工作。此外，数据必须在 Suite 之前使用逗号和空格进行格式化。我添加了一条重复记录（1 Burton Hills...）用于按逻辑测试组。

    Create Table Addresses (Address Varchar(8000))

Insert Addresses (Address) Values ('1 Burton Hills Blvd, Suite 250')
Insert Addresses (Address) Values ('1 Burton Hills Blvd, Suite 250')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Burton Hills Blvd')
Insert Addresses (Address) Values ('1 Ford Pl')
Insert Addresses (Address) Values ('1 Ford Pl, Suite 5B')
Insert Addresses (Address) Values ('1 Kish Dr')
Insert Addresses (Address) Values ('1 Park Center Dr, Suite 11')
Insert Addresses (Address) Values ('1 Park Center Dr')

;
With cteWithSuite As
    (
    Select Substring(Address, 1, CharIndex(', Suite', Address)-1) AddressRoot, Address
        From Addresses 
        Where CharIndex(', Suite', Address) > 0
        Group By Substring(Address, 1, CharIndex(', Suite', Address)-1), Address
    )
Update B Set B.Address = A.Address
    From cteWithSuite A
    Join (Select Address 
            From Addresses 
            Where CharIndex(', Suite', Address) = 0
        ) B On B.Address = A.AddressRoot
    • 1

相关问题