CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Extract specific string from variable

I have a Variable that contains dynamic string. I want to get the word that is equal to COATitle only.

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

 SELECT @String AS String

This string is being provided from front end so user can enter in different order as well. For example 

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'''

Expected output for above mentioned Strings are:

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

Upvotes: 1

Views: 42

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

One approach here is to just use base string functions in SQL Server. We can take a substring of the input, using the starting point as the text which immediately follows COATitle = ', continuing until just before the very next closing single quote.

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

enter image description here

Demo

Upvotes: 1

Related Questions