CHARINDEX не возвращает правильный индекс в строке поиска
У меня есть следующие данные в одном элементе в таблице базы данных:
Port Jefferson Station, NY 11776-2598
Northport, NY 11768
Garden City, NY 11530
Я пытаюсь разбить каждую строку на новую таблицу, содержащую поле города, поле штата и поле почтового индекса.
Я запускаю скрипт со следующим кодом, чтобы получить значение города:
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(',', @AddressCity, 0);
Кажется, он всегда возвращает значение 8 независимо от того, где в строке стоит запятая.
Есть идеи, почему?
Ответы 3
Конкретный ответ на ваш вопрос заключается в том, что вы определили @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);
Возвращает:
Это упрощенно и не охватывает крайние случаи, такие как отсутствие адреса или состояние с длиной, отличной от 2 символов. Но это все вещи, которые вы можете легко разобрать.
Смысл использования CROSS APPLY заключается в том, чтобы избежать повторения одних и тех же вычислений в нескольких местах, что потребовалось бы для запроса без CROSS APPLY.
Я ценю ваш вопрос/объяснение. Это всего лишь небольшая часть более крупного скрипта, который будет поступать от стороннего поставщика и нуждается в обмене сообщениями перед вставкой в наши таблицы. Что касается вашего первого пункта, ограничение в 8 символов было ошибкой, поскольку код был скопирован из другого источника, и мне не удалось это изменить. Спасибо!
Пожалуйста, попробуйте следующее решение.
Он основан на токенизации, а не на разборе строк.
SQL
-- 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);
Выход
Я склоняюсь к 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)
вы объявили переменную только размером 8