Get the word before a semicolon from a string in SQL Server

Viewed 1527

I have the below string:

Consolidation CompletedThe Scenario is LDP; the Year is 2018; the Start Period is July; the End Period is June; the Entity is TOT_NEWS.

And need to get the words just before the semicolon (;) and the last word before the dot (.)

Result:
-----  ----  ----  ----  ----
LDP    2018  July  June  TOT_NEWS

I could obtain the first one LDP with the below select:

REVERSE(LEFT(REVERSE(SUBSTRING(strDescription, CHARINDEX('The Scenario 
is', strDescription,+39), 
CHARINDEX(';',strDescription,-1))), CHARINDEX (' ', 
REVERSE(SUBSTRING(strDescription, 
CHARINDEX('The Scenario is', strDescription,+39), 
CHARINDEX(';',strDescription,-1)))))) as Scenario

But it doesn´t work for the rest of the string.

3 Answers
Related