选择nvarchar的子字符串max

Question

我有一个表是nvarchar（max）的列。我想搜索这个专栏，看看是否有具体的陈述。我可以做到这一点：

SELECT sectionText 
FROM Section 
WHERE sectionText LIKE '%This statement is true%' 

但是，这将返回整个部分的文本。我希望它只在单个列中返回此语句之前的50个字符和50个字符。

Answer 1

您可以尝试以下：

--This line would replace your phrase 
SELECT REPLACE(sectionText,'This statement is true','') 
FROM Section ; 

--Another way... 
--Assuming your column is: 50 characters + ' ' + 'This statement is true' + ' ' + The other 50 characters 
SELECT SUBSTRING(sectionText, 1, 50) + ' ' + SUBSTRING(sectionText, 74, 50) 
FROM Section 
WHERE sectionText LIKE '%This statement is true%'; 

您可以检查这个MSDN链接了解更多info。 让我提供简单的例子：

SELECT x = SUBSTRING('abcdef', 2, 3) 

SELECT x= SUBSTRING('abcdef', 2,1) + SUBSTRING('abcdef', 5,1) 

感谢@SeanLange评论（更好的方法）：

DECLARE @sectionText VARCHAR(1200) = 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut a dui quam. Sed pretium purus sed lorem volutpat fringilla. Sed imperdiet sodales justo, sed suscipit mi maximus ac. Aenean ligula quam, gravida a eleifend a, vulputate nec dolor. Donec sit amet mi in justo mollis mollis eu ac magna. Integer dolor nulla, mollis eu tempus et, lobortis in libero. Etiam vitae pulvinar mauris. Curabitur a nulla orci. This statement is true. Nam non est nec urna aliquam gravida. Sed diam lorem, finibus eu arcu et, iaculis ullamcorper diam. Sed vitae tellus odio. In hac habitasse platea dictumst. Maecenas lobortis consequat erat. Fusce aliquam imperdiet.'; 
DECLARE @MyPattern varchar(10) = 'This statement is true'; 

DECLARE @firstPosition INT, @lastPosition INT, @gambit INT, @textLenght INT; 

--Grabbing the positions 
SET @firstPosition = CHARINDEX(@sectionText, @sectionText,49) 
SET @gambit = LEN(@MyPattern) 
SET @lastPosition = @firstPosition + (@gambit + 1); --Sum 1 because of a White Space of a 
SET @textLenght = LEN(@sectionText); 

SELECT SUBSTRING(@sectionText, 1, @firstPosition) + ' ' + SUBSTRING(@sectionText, @lastPosition, @textLenght) AS [CleanText] 
--FROM Section 
--WHERE sectionText LIKE '%This statement is true%'; 

Answer 2

这是我能够做到：

SELECT SUBSTRING(sectionText, CHARINDEX('This statement is true', sectionText)-50, 100) 
FROM Section 
WHERE sectionText LIKE '%This statement is true%'