从字符串数据中获取5位邮政编码

Question

我有一个字符串，其中包含City、State Zip和Country。我已经能够将城市和州从字符串中提取到一个单独的列中，但我在提取邮政编码时遇到了问题。以下是我从中提取的数据示例。

哥伦布，俄亥俄州43219，美国

我试过的代码是这个

Replace(LTRIM(RTRIM(LEFT(C.[BILLING_CITYSTATEZIP], CHARINDEX(',', C.[BILLING_CITYSTATEZIP])))),',','')as City,
left(LTRIM(RTRIM(SUBSTRING(C.[BILLING_CITYSTATEZIP], CHARINDEX(',', C.[BILLING_CITYSTATEZIP])+1, LEN(C.[BILLING_CITYSTATEZIP])-(CHARINDEX('', C.[BILLING_CITYSTATEZIP])-1)))),2)as State,
dbo.udf_GetNumeric(Right(LTRIM(RTRIM(Substring(C.[BILLING_CITYSTATEZIP], CHARINDEX(',', C.[BILLING_CITYSTATEZIP])+1, LEN(C.[BILLING_CITYSTATEZIP])-(CHARINDEX('',C.[BILLING_CITYSTATEZIP]-1)))),2))as Zip,

我包含了我用来提取城市和州的代码，因为我将邮政编码的行从州行中镜像了出来，但我一直收到下面的错误。我想不出我在哪里错过了关于ltrim的争论。欢迎提出任何建议。

消息174，级别15，状态1，第36行ltrim函数需要1个参数。消息102，级别15，状态1，第52行"，"附近的语法不正确。

Answer 1

您可以使用这样的东西：

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

Answer 2

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

DECLARE @address NVARCHAR(500) = 'Columbus,OH 43219, United States'
SELECT SUBSTRING(SUBSTRING(SUBSTRING(@address, CHARINDEX(',',@address) + 1, LEN(@address)),0,CHARINDEX(',',SUBSTRING(@address, CHARINDEX(',',@address) + 1, LEN(@address)))),
CHARINDEX(' ',SUBSTRING(SUBSTRING(@address, CHARINDEX(',',@address) + 1, LEN(@address)),0,CHARINDEX(',',SUBSTRING(@address, CHARINDEX(',',@address) + 1, LEN(@address))))) + 1,LEN(@address))

Answer 3

对于"城市"，你有

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

对于"Zip"，在同一位置您有

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

请注意，结束括号是不同的。看起来你需要将其中一个移动到"之前"-1。

Answer 4

在处理复杂字符串表达式时，APPLY是您最好的朋友。您可以使用它使您的代码具有更多的DRYer(D关于非RepeatY我们自己(。这使得代码更加干净，更易于调试。

DECLARE @BILLING_CITYSTATEZIP VARCHAR(200) = ' Columbus ,OH 43219, 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(',',f.String,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,OH 43219, 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, 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)
FROM        (VALUES(RTRIM(LTRIM(REPLACE(REPLACE(
t.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(',',f.String,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