sql-server - 从变量中提取特定字符串

Question

我有一个包含动态字符串的变量。我想得到等于COATitleonly 的词。

DECLARE @String Varchar(250)
 SET @String = 'COATitle = ''Hello'' AND Date = ''2018-10-09'' AND Name = ''Warner Bros'''

 SELECT @String AS String

该字符串是从前端提供的，因此用户也可以按不同的顺序输入。例如

SET @String = 'Date = ''2018-10-09'' AND COATitle = ''Hello Tonia'' AND Name = ''Warner Bros'''
SET @String = 'COATitle = ''Hello'' AND Name = ''Warner Bros'' Date = ''2018-10-09'' AND '
SET @String = 'COATitle = ''Hello World'' AND Date = ''2018-10-09'' AND Name = ''Warner Bros'''
SET @String = 'COATitle = ''Piece of Cake'' AND Date = ''2018-10-09'' AND Name = ''Tim Martin'''

上述字符串的预期输出为：

1: Hello Tonia
2: Hello
3: Hello World
4: Piece of Cake

标签： sql-server

Answer 1

这里的一种方法是仅在 SQL Server 中使用基本字符串函数。我们可以取输入的一个子字符串，使用起始点作为紧随其后的文本COATitle = '，一直持续到下一个闭合单引号之前。

SELECT
    word,
    SUBSTRING(word,
              CHARINDEX('COATitle = ''', word) + 12,
              CHARINDEX('''', word, CHARINDEX('COATitle = ''', word) + 12) -
                  CHARINDEX('COATitle = ''', word) - 12) AS target
FROM yourTable;

演示