使用新行拆分SQL

Question

我有一个MS Sql数据库，其中包含以下数据：

ID   | ORD_No       | Date       | User       | Note       
-----+--------------+------------+------------+---------------
1    | 18/UT00120/ZS|            |            |---- Saved 10/10/2020 14:08 by John Snow, rest of the note 
                                               ----Saved on 11/11/2020 13:09 by Mike Kowalsky, rest of the   
                                                note ----  Saved on 12/11/2020 11:00 by Barbara Smith, rest of the note

在此基础上，我想创建以下输出：

ID   | ORD_No       | Date       | User           | Note       
-----+--------------+------------+----------------+---------------
1    | 18/UT00120/ZS| 10/10/2020 |  John Snow     | rest of the note
-----+--------------+------------+----------------+---------------
2    | 18/UT00120/ZS| 11/11/2020 |  Mike Kowalsky | rest of the note  
-----+--------------+------------+----------------+---------------
3    | 18/UT00120/ZS| 12/11/2020 |  Barbara Smith | rest of the note  

请告诉我怎样才能达到所需的输出。

谢谢!

Answer 1

SQL Server没有很好的字符串处理功能。您可以这样做，但这是相当痛苦的--而且对于音符可能看起来像什么的所有变化都不是很灵活。

一个大问题是内置的string_split()函数不接受多字符分隔符。下面选择一个不太可能出现在notes中的字符。

此外，前导前缀不一致--有些东西有"on“，有时没有。因此，这不会试图提取“字符串的其余部分”。它留在前缀中。您可以使用额外的字符串操作来处理此问题，但我怀疑真正的问题更为复杂。

在任何情况下，这都非常接近你想要的：

select t.id, t.ord_no, trim(s.value), s2.value as date
from t cross apply
     string_split(replace(note, '----', '~'), '~') s  cross apply
     (select top (1) s2.value
      from string_split(s.value, ' ') s2
      where try_convert(date, s2.value, 101) >= '2000-01-01'
     ) s2;

Here是一个db<>fiddle。

注意，使用日期不等性是因为select try_convert(date, '')返回'1900-01-01'，而不是我期望的NULL。

Answer 2

我想，我有一个解决方案。但是，在不同情况下，它可能无法工作。我已经使用了SUBSTRING、CHARINDEX、STRING_SPLIT、REPLACE和CAST来实现您想要的答案。下面是我给出的below=>代码

DECLARE @MyTable Table (ID INT, ORD_No VARCHAR(100),Note VARCHAR(300));
INSERT INTO @MyTable VALUES(1,'18/UT00120/ZS','Saved on 10/10/2020 14:08 by John Snow, rest of the note');
INSERT INTO @MyTable VALUES(2,'18/UT00120/ZS','Saved on 11/11/2020 07:08 by Mike Kowalsky, rest of the note');
INSERT INTO @MyTable VALUES(3,'18/UT00120/ZS','Saved on 12/11/2020 16:08 by Barbara Smith, rest of the note');
Select ID,ORD_No ,CAST(substring(Note,9,17) AS DATE) [Date],
       (SELECT top 1 value FROM STRING_SPLIT(SUBSTRING(Note,29,CHARINDEX(',',Note,0)),',')) AS [USER],
       RIGHT(REPLACE(SUBSTRING(Note, CHARINDEX(',', Note), LEN(Note)), '', ''), len(REPLACE(SUBSTRING(Note, CHARINDEX(',', Note), LEN(Note)), '', ''))-1) AS NOTE
FROM @MyTable

注意：只有当您的Note列数据的格式始终与您在问题中给出的格式相同时，此代码才有效。还要检查db-fiddle Link。