TSQL循环在DB字段中找到字符串表达式的所有出现

Question

我在SQL Server db表中有一个varchar(max)字段，我想在该字段中找到字符串的所有出现，并且在阅读时将其列出50个字符。P>

我已经使用下面的代码完成了此操作，但是现在我想对其进行调整以适合字符串的所有出现，并在一个字段中出现。

declare @extract varchar(max)
set @extract = 
'As Harry squelched along the deserted corridor he came across somebody who looked just as preoccupied as he was. Nearly Headless Nick, the ghost of Gryffindor Tower, was staring morosely out of a window, muttering under his breath, ". . . don''t fulfill their requirements . . . half an inch, if that . . ." 
"Hello, Nick," said Harry. 
"Hello, hello," said Nearly Headless Nick, starting and looking round. He wore a dashing, plumed hat on his long curly hair, and a tunic with a ruff, which concealed the fact that his neck was almost completely severed. He was pale as smoke, and Harry could see right through him to the dark sky and torrential rain outside. 
"You look troubled, young Potter," said Nick, folding a transparent letter as he spoke and tucking it inside his doublet. 
"So do you," said Harry.'
declare @searchterm varchar(max)
set @searchterm = '%Harry%'
declare @searchtermlength int = len(@searchterm)
declare @stringFrom int = (select PATINDEX(@searchterm,@extract) - 50 )
declare @stringto int = (select PATINDEX(@searchterm,@extract) + @searchtermlength + 50 )
declare @noChars int = @stringto - @stringfrom
select SUBSTRING(@extract,@stringFrom,@noChars)

这返回'As Harry squelched along the deserted corridor he came acros'，但我希望它返回：

As Harry squelched along the deserted corridor he came acros...
...f an inch, if that . . ." "Hello, Nick," said Harry. "Hello, hello," said Nearly Headless Nick, sta
...ost completely severed. He was pale as smoke, and Harry could see right through him to the dark sky and tor...
...king it inside his doublet. "So do you," said Harry.

我猜想这可以使用循环完成，但是我以前从未真正使用过一个循环，因此任何帮助都将不胜感激。

预先感谢

Answer 1

希望这就是您正在查看的

DECLARE @extract NVARCHAR(MAX)
DECLARE @out_table TABLE (string NVARCHAR(MAX))
DECLARE @search_string NVARCHAR(20)
DECLARE @pos INT
DECLARE @start_pos INT
DECLARE @trim_len INT
DECLARE @string_len INT
DECLARE @search_string_len INT
DECLARE @old_pos INT
DECLARE @start_dots NVARCHAR(3)
DECLARE @end_dots NVARCHAR(3)
SET @extract ='As Harry squelched along the deserted corridor he came across somebody who looked just as preoccupied as he was. Nearly Headless Nick, the ghost of Gryffindor Tower, was staring morosely out of a window, muttering under his breath, ". . . don''t fulfill their requirements . . . half an inch, if that . . ." 
"Hello, Nick," said Harry. 
"Hello, hello," said Nearly Headless Nick, starting and looking round. He wore a dashing, plumed hat on his long curly hair, and a tunic with a ruff, which concealed the fact that his neck was almost completely severed. He was pale as smoke, and Harry could see right through him to the dark sky and torrential rain outside. 
"You look troubled, young Potter," said Nick, folding a transparent letter as he spoke and tucking it inside his doublet. 
"So do you," said Harry.'
SET @search_string = 'Harry'
SET @string_len = LEN(@extract)
SET @search_string_len = LEN(@search_string)
SET @old_pos = 0
SET @pos = PATINDEX('%' + @search_string + '%',@extract) 
WHILE @pos > 0 AND @old_pos <> @pos
BEGIN
    SET @start_dots = '...'
    SET @end_dots = '...'
    IF (@pos - 50) < 1
    BEGIN
        SET @start_pos = 1
        SET @start_dots = ''
    END
    ELSE
    BEGIN
        SET @start_pos = @pos - 47  -- 3 chars for the dots
    END
    IF (@pos + @search_string_len + 50) > @string_len
    BEGIN
        SET @trim_len = (@string_len - @start_pos) + 1
        SET @end_dots = ''
    END
    ELSE
    BEGIN
        SET @trim_len = (@pos - @start_pos) + @search_string_len + 47  -- 3 chars for the dots
    END
    INSERT INTO @out_table
    SELECT @start_dots + SUBSTRING(@extract, @start_pos, @trim_len) + @end_dots
    SET @old_pos = @pos
    SET @pos = PATINDEX('%' + @search_string + '%',SUBSTRING(@extract, @pos + 1,LEN(@extract))) + @pos
END
SELECT * FROM @out_table

输出

As Harry squelched along the deserted corridor he came ...
... an inch, if that . . ."   "Hello, Nick," said Harry.   "Hello, hello," said Nearly Headless Nick, ...
... completely severed. He was pale as smoke, and Harry could see right through him to the dark sky an...
...ing it inside his doublet.   "So do you," said Harry.