Extract specific string from variable

Question

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

Tim Biegeleisen · Accepted Answer · 2018-10-29 06:04:31Z

1

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;

Demo

answered Oct 29, 2018 at 6:04

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract specific string from variable

1 Answer 1

Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related