SQL-提取两个字符之间的子字符串

使用“反向”可以处理以下内容:

SELECT reverse(
            left(
             reverse(
               left(
                 Location, 
                 CHARINDEX(',', location)-1
               )
              ),
              CHARINDEX(' ', reverse(
                 left(
                   Location, 
                   CHARINDEX(',', location)-1
                 )
               )
             )
           )
  )as city FROM table;

Fiddle

如果我在上面的评论中提到的Google API不是一个选项。您可以下载(甚至购买)邮政编码数据库。费用是名义上的。我建议每季度更新一次,因为邮政编码会随着时间而变化(添加/编辑/删除)

例子

Declare @YourTable table (id int,addr varchar(250))
Insert Into @YourTable values
(1,'701 MONROE STREET NW RUSSELLVILLE, AL 35653 (34.514971, -87.736372)'),
(2,'1825 HOLTVILLE ROAD WETUMPKA, AL 36092 (32.558544, -86.221265)')

Select A.ID
      ,StreetAddress =left(addr,nullif(charindex(Z.CityName,addr),0)-1)
      ,Z.CityName
      ,Z.StateCode
      ,Z.ZIPCode
 From @YourTable A
 Join [dbo].[OD-Zip] Z 
   on Z.ZipCode = substring(addr,nullif(patindex('%[0-9][0-9][0-9][0-9][0-9]%',addr),0),5)
      and charindex(Z.CityName,addr)>0
      and Z.ZipType='S'
      and Z.CityType='D'

退换商品

ID  StreetAddress           CityName        StateCode   ZIPCode
1   701 MONROE STREET NW    Russellville    AL          35653
2   1825 HOLTVILLE ROAD     Wetumpka        AL          36092