如何根据模式分割TEXT值并将结果返回到TSQL表中

Question

我有这个字符串，它存储在文本字段中。存储这些信息的过程已经发生了变化，但我需要这些“传统”信息。

23.06.17 - As per quote #8918.1. 7m Swing STage to Positon 1. 
05.07.17 Stripped and rebuilt boat ready for positon 4 but rained off. 
05.07.17 Stripped and rebuilt boat ready for positon 4 but rained off. 
06.07.17 - Completion of Swing Stage to Positon 4. 
21.07.17 - Built Access Tower to get acess to roof. 
Got swing Stage onto roof. 
No access to building unable to get parapit clamps and wire up. 
21.07.17 - Built Access Tower to get acess to roof. 
Got swing Stage onto roof. 
No access to building unable to get parapit clamps and wire up. 

我怎么会由拆分日期字段这个文本变成结果的表，所以每一个新的日期，一个新行（含当日）所示。那么在上面的例子中，会有6行？

这是我想出了作为一个概念证明：

DECLARE @MEMO VARCHAR(MAX) 
    SET @MEMO = '23.06.17 test 1 24.06.17 test 2 25.06.17 test 3' 

DECLARE @COUNTER INT 
    SET @COUNTER=0 

DECLARE @WORD VARCHAR(40) 
    SET @WORD = '' 

DECLARE @DATE VARCHAR(10) 
    SET @DATE = '' 

DECLARE @LINE VARCHAR(500) 
    SET @LINE = '' 

WHILE @COUNTER <= LEN(@MEMO)+1 BEGIN 
    IF SUBSTRING(@MEMO, @COUNTER, 1) != ' ' BEGIN 
     --Builds each character into a string. 
     SET @WORD = @WORD + SUBSTRING(@MEMO, @COUNTER, 1) 
     -- When we come across a space, assume the previous character is now a whole word. 
    END ELSE IF SUBSTRING(@MEMO, @COUNTER, 1) = ' ' BEGIN 
     IF @WORD NOT LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9]' BEGIN 
      SET @LINE = @LINE + ' ' + @WORD 
      SET @WORD = '' 
     --If that word is a date string format then save it and reset. 
     END ELSE IF @WORD LIKE '[0-9][0-9].[0-9][0-9].[0-9][0-9]' BEGIN 
      SET @DATE = @WORD 
      SET @LINE = '' 
      SET @WORD = '' 
      PRINT 'DATE: ' + @DATE 
     END 
     IF LTRIM(@LINE) != '' BEGIN 
      PRINT 'LINE: ' + LTRIM(@LINE) 
     END 
    END 
    SET @COUNTER = @COUNTER + 1 
END 

，但我的结果，是有点过仍。我无法弄清楚，一旦完成后，如何才能显示该行。

DATE: 23.06.17 
LINE: test 
LINE: test 1 
DATE: 24.06.17 
LINE: test 
LINE: test 2 
DATE: 25.06.17 
LINE: test 
LINE: test 3 

最终的目标基本上是这种情况，例如

╔═══╦════════════╦═════════════╗ 
║ ║ Date  ║ Value  ║ 
╠═══╬════════════╬═════════════╣ 
║ 1 ║ 2017-06-23 ║ Test 1  ║ 
║ 2 ║ 2017-06-24 ║ Test 2  ║ 
║ 3 ║ 2017-06-25 ║ Test 3  ║ 
╚═══╩════════════╩═════════════╝ 

Answer 1

编辑 - 修正了错字

一种方法是将字符串解析成词，然后重建。

我使用的表值函数分割字符串，但容易地转化为在线方法（无UDF）

例

Declare @YourTable table (ID int,Memo varchar(max)) 
Insert Into @YourTable values 
(1,'23.06.17 - As per quote #8918.1. 7m Swing STage to Positon 1. 
05.07.17 Stripped and rebuilt boat ready for positon 4 but rained off. 
05.07.17 Stripped and rebuilt boat ready for positon 4 but rained off. 
06.07.17 - Completion of Swing Stage to Positon 4. 
21.07.17 - Built Access Tower to get acess to roof. 
Got swing Stage onto roof. 
No access to building unable to get parapit clamps and wire up. 
21.07.17 - Built Access Tower to get acess to roof. 
Got swing Stage onto roof. 
No access to building unable to get parapit clamps and wire up.') 
,(2,'23.06.17 test 1 24.06.17 test 2 25.06.17 test 3') 


;with cte as (
     Select A.ID 
       ,B.* 
     From @YourTable A 
     Cross Apply (
         Select * 
           ,Flg = case when RetVal like '[0-9][0-9].[0-9][0-9].[0-9][0-9]' then 1 else 0 end 
           ,Grp = sum(case when RetVal like '[0-9][0-9].[0-9][0-9].[0-9][0-9]' then 1 else 0 end) over(Order By RetSeq) 
         From [dbo].[tvf-Str-Parse](replace(Memo,char(13)+char(10),' '),' ') 
        ) B 
) 
Select ID 
     ,LineNbr = Grp 
     ,Date = convert(date,RetVal,4) 
     ,Text = Stuff((Select Top 1000 ' ' +RetVal From cte Where ID=A.ID and Grp=A.Grp and Flg=0 Order By RetSeq For XML Path ('')),1,1,'') 
From cte A 
Where Flg=1 
Order By ID,Grp 

返回

The如果有兴趣的

CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10)) 
Returns Table 
As 
Return ( 
    Select RetSeq = Row_Number() over (Order By (Select null)) 
      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)'))) 
    From (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i) 
); 
--Thanks Shnugo for making this XML safe 
--Select * from [dbo].[tvf-Str-Parse]('Dog,Cat,House,Car',',') 
--Select * from [dbo].[tvf-Str-Parse]('John Cappelletti was here',' ') 

Answer 2

该解决方案是基于一个目的建立iTVF基于递归CTE解析/分割UDF。

功能代码：

ALTER FUNCTION dbo.ParseLegacyInfo 
/* =================================================================== 
Created to parse the text information from legacy system. 
==================================================================== */ 
(
    @TextString VARCHAR(8000) 
) 
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN 
    WITH 
     cte_rSplit AS (
      SELECT 
       LineNum = 1, 
       ParsedString = SUBSTRING(@TextString, 1, dl.DateLocation + 7), 
       StringRemain = SUBSTRING(@TextString, dl.DateLocation + 8, 8000) 
      FROM 
       (VALUES(ISNULL(NULLIF(PATINDEX('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%', STUFF(@TextString, 1, 8, '')), 0), 8000))) dl (DateLocation) 
      UNION ALL 
      SELECT 
       LineNum = rs.LineNum + 1, 
       ParsedString = SUBSTRING(rs.StringRemain, 1, dl.DateLocation + 7), 
       StringRemain = SUBSTRING(rs.StringRemain, dl.DateLocation + 8, 8000) 
      FROM    
       cte_rSplit rs 
       CROSS APPLY (VALUES(ISNULL(NULLIF(PATINDEX('%[0-9][0-9].[0-9][0-9].[0-9][0-9]%', STUFF(rs.StringRemain, 1, 8, '')), 0), 8000))) dl (DateLocation) 
      WHERE 
       rs.StringRemain LIKE '%[0-9][0-9].[0-9][0-9].[0-9][0-9]%' 
      ) 
    SELECT 
     rs.LineNum, 
     [Date] = CONVERT(DATE, LEFT(rs.ParsedString, 8), 4), 
     TextValue = SUBSTRING(rs.ParsedString, 9, 8000) 
    FROM 
     cte_rSplit rs; 
GO 

一些测试数据...

IF OBJECT_ID('tempdb..#LegacyInfo', 'U') IS NOT NULL 
DROP TABLE #LegacyInfo; 

CREATE TABLE #LegacyInfo (
    id INT NOT NULL IDENTITY(1, 1), 
    TextData VARCHAR(8000) NOT NULL 
    ); 
INSERT #LegacyInfo (TextData) VALUES 
('23.06.17 - As per quote #8918.1. 7m Swing STage to Positon 1. 
05.07.17 Stripped and rebuilt boat ready for positon 4 but rained off. 
05.07.17 Stripped and rebuilt boat ready for positon 4 but rained off. 
06.07.17 - Completion of Swing Stage to Positon 4. 
21.07.17 - Built Access Tower to get acess to roof. 
Got swing Stage onto roof. 
No access to building unable to get parapit clamps and wire up. 
21.07.17 - Built Access Tower to get acess to roof. 
Got swing Stage onto roof. 
No access to building unable to get parapit clamps and wire up.'), 
('23.06.17 - As per quote #8918.1. 7m Swing STage to Positon 1. 
05.07.17 Stripped and rebuilt boat ready for positon 4 but rained off. 
05.07.17 Stripped and rebuilt boat ready for positon 4 but rained off. 
06.07.17 - Completion of Swing Stage to Positon 4. 
21.07.17 - Built Access Tower to get acess to roof. 
Got swing Stage onto roof. 
No access to building unable to get parapit clamps and wire up. 
21.07.17 - Built Access Tower to get acess to roof. 
Got swing Stage onto roof. 
No access to building unable to get parapit clamps and wire up.'), 
('31.12.17 - This is just a test...'), 
('01.01.17 - Line 1 
Some other text following line 1. 
22.02.17 - Line 2 
More stuff about line 2.'); 

最后的查询...

SELECT 
    li.id, LineNum, Date, TextValue 
FROM 
    #LegacyInfo li 
    CROSS APPLY dbo.ParseLegacyInfo(li.TextData); 

结果......（注：原carage退货和换行依然保持完好）。