Get a word from a string in SQL Server 2008

The answer does not have to be complicated, it is enough to first get what is before the ; , then, make a reverse to that chain to remove what is after the space, and finally, return the string to its original cropped state.

Taking the following code as an example, you can find out:

DECLARE @cadena VARCHAR(64)
DECLARE @cadena_pivote VARCHAR(64)
SET @cadena = 'The Scenario is BUDGET; the Year is 2018'

SET @cadena_pivote = REPLACE(@cadena, SUBSTRING(@cadena, CHARINDEX(';', @cadena), LEN(@cadena)), '')

SELECT @cadena_pivote ----> Resultado: The Scenario is BUDGET

SELECT REVERSE(LEFT(REVERSE(@cadena_pivote), CHARINDEX(' ', REVERSE(@cadena_pivote))-1))
----> Resultado: BUDGET

Here you can see the demo and its results

These are examples where one misses regular expressions. Solving your problem implies that one must look for certain characters starting from the left in some cases and from the end in others, for example: the ; is easy to locate, now the previous space can not be calculated in the same way because we would identify someone earlier, the ideal is to "turn" the text and just tell there. For this we use REVERSE() , then it is simply adjust the calculation with those two numbers:

DECLARE @Ejemplo  VARCHAR(255)

SELECT  @Ejemplo = 'The Scenario is BUDGET; the Year is 2018'

SELECT  @Ejemplo,
    SUBSTRING(@Ejemplo, 
                CHARINDEX(';', @Ejemplo) - CHARINDEX(' ',REVERSE(SUBSTRING(@Ejemplo, 1, CHARINDEX(';', @Ejemplo)))) + 2,
                CHARINDEX(' ',REVERSE(SUBSTRING(@Ejemplo, 1, CHARINDEX(';', @Ejemplo)))) - 2
    ),
    SUBSTRING(@Ejemplo, 
            LEN(@Ejemplo)-CHARINDEX(' ',REVERSE(@Ejemplo)) + 2 , 
            LEN(@Ejemplo)
    )

The exit:

======================================== ====== ====
The Scenario is BUDGET; the Year is 2018 BUDGET 2018    

Note: It is convenient to do a RTRIM() previously to the string to analyze since a case where there is a space after the year will fail.

What happens is that the SUBSTRING syntax works in the following way:

SUBSTRING ( expresion, comienzo, longitud)

Where:

• Expression is the character string from which you want to extract characters
• Start is the entire expression that specifies where the set of returned characters will begin.
• Length as its name indicates, how many characters you want to return from start .

If you know the length of the word to search would be useful.

Now if not, you can use the following:

USE[my_db]
SELECT SUBSTRING(strDescription, 22, CHARINDEX(';', strDescription)-22)
FROM TECHBUD_TASK_AUDI

With this you tell the substring to start at position 22 (which I assume is always the same, otherwise it will not work correctly) and in the length I specify a subtraction between the location of the character (';') and the 22 that you already have considered, which would bring you the word that is just before that particular character. I hope my solution has served you, greetings.