Извлечение и обработка подстроки между различным количеством разделителей в строке переменной длины
У меня есть таблица dates со столбцом SENTDATE, где каждая строка содержит различное количество дат, разделенных точкой с запятой (;). Даты в настоящее время имеют формат ГГГГММДД, например:
20240529;20240626;20240626;20240626;20240626;20240626
20240530;20240620
20240605
20240523;20240523
Я пытаюсь преобразовать каждую из этих дат в формат ДД/ММ/ГГГГ и разделить их точкой с запятой, чтобы они выглядели следующим образом:
29/05/2024;26/06/2024;26/06/2024;26/06/2024;26/06/2024
30/05/2024;20/06/2024
К сожалению, я использую SQL Server 2005, поэтому для изменения форматов мне нужно использовать SUBSTRING, а затем объединить разные части дат, перемежающиеся косыми чертами:
SUBSTRING(SENTDATE, 1, 4) + '/' + SUBSTRING(SENTDATE, 5, 2) + '/' + SUBSTRING(SENTDATE, 7, 2)
но это не проблема.
Знаете ли вы, как можно обрабатывать каждую дату между точками с запятой? Я предполагаю, что это будет своего рода разделение, а затем рекомбинация строки?
Я здесь вообще ничего не пробовал. Я мог бы использовать PARSENAME, но понятия не имею, как это сделать с разным количеством разделителей.
Спасибо.
@MartinSmith Согласен, но эти таблицы были спроектированы и созданы в начале 2000-х годов - к сожалению, я ничего не могу сделать, чтобы изменить это сейчас.
Существует целая куча вопросов и ответов о том, как разделить строки и как их снова объединить... например. просто быстрый поиск stackoverflow.com/questions/5493510/…
Вы действительно все еще используете SQL Server 2005? Срок службы этой версии истек еще в 2016 году; Давно прошло время, когда вы реализовали план обновления, который завершили около десяти лет назад. В 2005 не хватает поддержки многих функций, которые сейчас считаются само собой разумеющимися, поскольку они существуют уже десять лет или больше.
Если бы вы использовали современную поддерживаемую версию SQL Server, у вас, вероятно, был бы доступ к STRING_SPLIT() и STRING_AGG().
Ответы 2
Вам действительно следует изменить схему вашей таблицы, чтобы она соответствовала основным правилам нормализации базы данных. Однако, если вам придется иметь дело с этой плохой схемой, вы можете сделать следующее:
Пример данных и запрос
DECLARE @t TABLE (Id INT , SENTDATE VARCHAR(1000))
INSERT INTO @t (Id , SENTDATE)
VALUES
( 1 , '20240529;20240626;20240626;20240626;20240626;20240626')
,( 2 , '20240530;20240620')
,( 3 , '20240605')
,( 4 , '20240523;20240523');
WITH CTE AS
(
SELECT A.Id
, CONVERT(VARCHAR(10) , CAST(Split.a.value('.', 'VARCHAR(100)') AS DATETIME) , 103) AS SENTDATE
FROM
(
SELECT Id,
CAST ('<Row>' + REPLACE(SENTDATE, ';', '</Row><Row>') + '</Row>' AS XML) AS Data
FROM @t
) AS A CROSS APPLY Data.nodes ('/Row') AS Split(a)
)
SELECT ID
,STUFF((SELECT '; ' + CAST(SENTDATE AS VARCHAR(10)) [text()]
FROM CTE
WHERE ID = t.ID
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM CTE t
GROUP BY ID
Выход
+----+-------------------------------------------------------------------------+
| ID | List_Output |
+----+-------------------------------------------------------------------------+
| 1 | 29/05/2024; 26/06/2024; 26/06/2024; 26/06/2024; 26/06/2024; 26/06/2024 |
| 2 | 30/05/2024; 20/06/2024 |
| 3 | 05/06/2024 |
| 4 | 23/05/2024; 23/05/2024 |
+----+-------------------------------------------------------------------------+
Примечание
Я почти уверен, что такие понятия, как FOR XML PATH, CROSS APPLY и CTE, были представлены в SQL Server 2005, поэтому это решение должно вам подойти.
@MartinSmith, о да, это звонит. Я обновлю свое решение, чтобы вместо этого использовать datetime, оно также должно работать с Datetime.
Спасибо большое - это были нужные знания. Я бы проголосовал за это, но у меня пока нет такой репутации, но с небольшими изменениями и поправками для работы с моим набором данных это сработало - спасибо!
@MeltedMetal, все в порядке, я рад, что это помогло. Вы можете принять мой ответ, это поможет и другим пользователям.
Попробуйте следующее решение, использующее токенизацию через XML и XQuery SQL Server.
Он будет работать, начиная с SQL Server 2005.
Примечательные моменты:
CROSS APPLYпреобразует входную строку в XML для токенизации.- Метод XQuery
.query(), преобразующий отдельные значения даты в желаемый формат в выражении FLWOR. - Метод XQuery
.value()в предложенииSELECTвозвращает XML обратно в строку.
SQL
DECLARE @tbl TABLE (Id INT IDENTITY PRIMARY KEY, SENTDATE VARCHAR(1000));
INSERT INTO @tbl (SENTDATE) VALUES
('20240529;20240626;20240626;20240626;20240626;20240626'),
('20240530;20240620'),
('20240605'),
('20240523;20240523');
DECLARE @separator CHAR(1) = ';';
SELECT t.*, c
, REPLACE(c.query('data(/root/r)').value('.','VARCHAR(1000)'), SPACE(1), @separator) AS result
FROM @tbl AS t
CROSS APPLY (SELECT CAST('<root><r><![CDATA[' +
REPLACE(SENTDATE, @separator, ']]></r><r><![CDATA[') +
']]></r></root>' AS XML).query('<root>{
for $x in /root/r/text()
return <r>{concat(substring($x,7,2),"/", substring($x,5,2),"/",substring($x,1,4))}</r>
}</root>')) AS t1(c);
Выход
Хранить строку с разделителями в базе данных — плохая идея. Это должно храниться как строка для каждого значения и использовать тип данных date/datetime, а не какую-либо строку.