您可以使用以下内容：

select case when patindex('% [0-9][0-9][0-9][0-9][0-9],%',c) > 0 then
SUBSTRING(c,patindex('% [0-9][0-9][0-9][0-9][0-9],c) + 1,5)
else '' end as zipCode
from
(
select 'Columbus,OH 43219,United States' as c
union all
select 'Chicago,IL 60626,United States' as c
) c

这是使用子字符串的一种方法：

DECLARE @address NVARCHAR(500) = 'Columbus,United States'

SELECT SUBSTRING(SUBSTRING(SUBSTRING(@address,CHARINDEX(',',@address) + 1,LEN(@address)),SUBSTRING(@address,LEN(@address)))),CHARINDEX(' ',SUBSTRING(SUBSTRING(@address,LEN(@address))))) + 1,LEN(@address))

对于“城市”，埋葬在这里

- (CHARINDEX('',C.[BILLING_CITYSTATEZIP])-1)

对于“邮编”，您在同一位置

 - (CHARINDEX('',C.[BILLING_CITYSTATEZIP]-1))

请注意，右括号是不同的。看来您需要将这两个之一移到“ -1”之前。

在处理复杂的字符串表达式时，APPLY是您最好的朋友。您可以使用它使您的代码更加DRYer（ D 而不是 R 自己使用 Y ）。这使代码更简洁，更易于调试。

DECLARE @BILLING_CITYSTATEZIP VARCHAR(200) = ' Columbus,United States ';

SELECT
  City    = SUBSTRING(f.String,a.Head,a.Pos-1),[State] = SUBSTRING(f.String,a.Pos+1,b.Pos-a.Pos-1),Zip     = SUBSTRING(f.String,b.Pos+1,c.Pos-b.Pos-1),Country = SUBSTRING(f.String,c.Pos+1,200) -- BONUS
FROM        (VALUES(RTRIM(LTRIM(REPLACE(REPLACE( -- All trimming (outer & inner) in 1 spot
             @BILLING_CITYSTATEZIP,'),'))))) AS f(String)
CROSS APPLY (VALUES(CHARINDEX(',f.String),1))            AS a(Pos,Head)
CROSS APPLY (VALUES(CHARINDEX(' ',f.String,a.Pos+1)))      AS b(Pos)
CROSS APPLY (VALUES(CHARINDEX(',b.Pos+1)))      AS c(Pos);

返回：

City        State    Zip     Country
----------- -------- ------- -----------------
Columbus    OH       43219   United States

针对一个表格，它看起来像这样：

DECLARE @table TABLE (SomeId INT IDENTITY,BILLING_CITYSTATEZIP VARCHAR(200));
INSERT  @table VALUES ('Columbus,United States'),('Chicago,IL 60625,United States')

SELECT t.SomeId,f.City,f.[State],f.[Zip],f.Country 
FROM   @table AS t
CROSS APPLY
(
  SELECT
    City    = SUBSTRING(f.String,200)
  FROM        (VALUES(RTRIM(LTRIM(REPLACE(REPLACE(
               t.BILLING_CITYSTATEZIP,'))))) AS f(String)
  CROSS APPLY (VALUES(CHARINDEX(',Head)
  CROSS APPLY (VALUES(CHARINDEX(' ',a.Pos+1)))      AS b(Pos)
  CROSS APPLY (VALUES(CHARINDEX(',b.Pos+1)))      AS c(Pos)
) AS f;

返回：

SomeId   City       State  Zip    Country
-------- ---------- ------ ------ ----------------
1        Columbus   OH     43219  United States
2        Chicago    IL     60625  United States