CHARINDEX没有返回正确的索引到搜索字符串

Question

我在一个数据库表的单个元素中有以下数据:

Port Jefferson Station, NY 11776-2598   
Northport, NY 11768                     
Garden City, NY 11530                   

我试图将每行分成一个新表，其中包含一个城市字段，一个州字段和一个邮政编码字段。

我正在运行一个脚本，其中包含以下代码来获取City值:

Create function [dbo].[fnBuildCityState](@AddressCity varchar(40))
RETURNS @NameTable TABLE
(
City VARCHAR(255),
State VARCHAR(255),
Zip VARCHAR(255)
) 
as
BEGIN
-- Index and word variables.
DECLARE @AccountNameEndIndex INT;
DECLARE @FirstWordEndIndex   INT;
DECLARE @SecondWordEndIndex  INT;
DECLARE @ThirdWordEndIndex   INT;
DECLARE @FirstWord       NVARCHAR(8);
DECLARE @SecondWord      NVARCHAR(50);
DECLARE @ThirdWord       NVARCHAR(50);
SET @AddressCity = RTRIM(ISNULL(@AddressCity,'No Address, City ZipCode'))
if (LEN(@AddressCity)<3)
BEGIN
SET @AddressCity = 'No Address, City ZipCode'
END
-- Get the last index.
SET @AccountNameEndIndex = LEN(@AddressCity) - 1;
-- Get the first word.
SET @FirstWordEndIndex = CHARINDEX(',', @AddressCity, 0);
SET @FirstWord         = SUBSTRING(@AddressCity, 0, @FirstWordEndIndex);

INSERT INTO @NameTable (City, State, Zip)
VALUES (@FirstWord, @SecondWord, @ThirdWord)
RETURN
END

当我运行代码并查看结果表时，我看到了如下输出:

City                     State                Zip
Port Jef                 NULL                 NULL
Northpor                 NULL                 NULL
Garden C                 NULL                 NULL

返回输入中逗号所在位置的索引的那行代码是

SET @FirstWordEndIndex = CHARINDEX('，'， @ addressity, 0);

无论逗号在字符串中的哪个位置，它似乎总是返回值8。

知道为什么吗?

Answer 1

您的问题的具体答案是因为您将@FirstWord定义为NVARCHAR(8)，因此它只包含最多8个字符。

但更大的问题是你为什么要尝试做那个RBAR?SQL是基于集合的是有原因的…你可以构建一个查询来完成你需要的一切，例如:

SELECT b.City, C.[State], c.ZipCode
FROM (
VALUES
('Port Jefferson Station, NY 11776-2598'),
('Northport, NY 11768'),
('Garden City, NY 11530')
) x ([Address])
CROSS APPLY (
VALUES
(CHARINDEX(',', x.[Address], 0))
) a (FirstComma)
CROSS APPLY (
VALUES
(SUBSTRING(x.[Address],1,a.FirstComma-1)
, SUBSTRING(x.[Address],a.FirstComma+2,LEN(x.[Address])))
) b (City,SecondString)
CROSS APPLY (
VALUES
(SUBSTRING(b.SecondString,1,2)
, SUBSTRING(b.SecondString,4,LEN(b.SecondString)))
) c ([State],ZipCode);

的回报:

<表类>城市国家ZipCodetbody><<tr>杰弗逊站纽约11776 - 2598Northport纽约11768花园城市纽约11530

请尝试以下解决方案。

它基于标记化而不是字符串解析。

/p>

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(100));
INSERT @tbl (tokens) VALUES
('Port Jefferson Station, NY 11776-2598'),
('Northport, NY 11768'),   
('Garden City, NY 11530');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = ',';
SELECT t.*
, city = c.value('(/root/r[1]/text())[1]', 'VARCHAR(50)')
, state = LEFT(TRIM(c.value('(/root/r[2]/text())[1]', 'VARCHAR(50)')), 2)
, zip = SUBSTRING(TRIM(c.value('(/root/r[2]/text())[1]', 'VARCHAR(50)')), 3, 50)
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
']]></r></root>' AS XML)) AS t1(c);

<表类>ID标记城市国家zip1纽约杰弗逊港车站11776-2598纽约杰弗逊港车站纽约11776-25982Northport,纽约11768Northport纽约117683花园城市,纽约11530花园城市纽约11530

在解析字符串时，我倾向于使用JSON

Select City  = JSON_VALUE(JS1,'$[0]')
,State = JSON_VALUE(JS2,'$[0]')
,ZIP   = JSON_VALUE(JS2,'$[1]')
From YourTable A
Cross Apply (values ('["'+replace(Address,',','","')+'"]') ) B(JS1)
Cross Apply (values ('["'+replace(ltrim(JSON_VALUE(JS1,'$[1]')),' ','","')+'"]') ) C(JS2)

结果

City                    State   ZIP
Port Jefferson Station  NY      11776-2598
Northport               NY      11768
Garden City             NY      11530

如果你想使用作为TVF

Declare @AddressCity varchar(max) = 'Port Jefferson Station, NY 11776-2598'
Select City  = JSON_VALUE(JS1,'$[0]')
,State = JSON_VALUE(JS2,'$[0]')
,ZIP   = JSON_VALUE(JS2,'$[1]')
From (values ('["'+replace(@AddressCity,',','","')+'"]') ) A(JS1)
Cross Apply (values ('["'+replace(ltrim(JSON_VALUE(JS1,'$[1]')),' ','","')+'"]') ) B(JS2)