How do i pull the next 2 words after a specific string?

I am spending a long time on how to pull part of a long string in T-SQL. I need to pull the person after "Eaten by" string. In this case, the first string below will be Smith, John and for the second string it will be Bloggs, Joe

Each string length is never the same, however the spacing is always consistent. I don't know whether to use SUBSTRING,CHARINDEX or PATINDEX or a combination of these, I have attempted all these, but been unsuccessful.

Example String 1 'Apple: Two days Pear: Three days Banana: One day Eaten by: Smith, John Location 1: First Street Location 2: Second Street'

Example String 2 'Pear: Three days Banana: One day Eaten by: Bloggs, Joe Location 1: First Street Location 2: Second Street Location 3: Third Street'

Any help with be appreciated. Thanks.

SUBSTRING(SUBSTRING(@string,charindex('Eaten by:',@string) + LEN('Eaten by:'), LEN(@string)), 2, CHARINDEX(' ', SUBSTRING(@string,charindex('Eaten by:',@string) + LEN('Eaten by:'), LEN(@string)), CHARINDEX(' ', SUBSTRING(@string,charindex('Eaten by:',@string) + LEN('Eaten by:'), LEN(@string)), 2)+1))

The output was:

Smith, John Location 1: First Street Location 2: Second Street

I need "Smith, John' only.

Solution

There's probably a better solution out there but I'm just using the code that you provided: you can use a combination of LTRIM() and RTRIM() for SQL Server 2016 to trim whitespaces. I also adjusted your first inner SUBSTRING() from 2 to 1 to get the results that you're looking for:

DECLARE @string NVARCHAR (4000)
SET @string = 'Apple: Two days Pear: Three days Banana: One day Eaten by: Smith, John Location 1: First Street Location 2: Second Street'
SELECT LTRIM(RTRIM(SUBSTRING(SUBSTRING(@string,charindex('Eaten by:',@string) + LEN('Eaten by:'), LEN(@string)), 1, CHARINDEX(' ', SUBSTRING(@string,charindex('Eaten by:',@string) + LEN('Eaten by:'), LEN(@string)), CHARINDEX(' ', SUBSTRING(@string,charindex('Eaten by:',@string) + LEN('Eaten by:'), LEN(@string)), 2)+1)))) AS Name;

Result:

Name
Smith, John

DECLARE @string NVARCHAR (4000)
SET @string = 'Pear: Three days Banana: One day Eaten by: Bloggs, Joe Location 1: First Street Location 2: Second Street Location 3: Third Street'
SELECT LTRIM(RTRIM(SUBSTRING(SUBSTRING(@string,charindex('Eaten by:',@string) + LEN('Eaten by:'), LEN(@string)), 1, CHARINDEX(' ', SUBSTRING(@string,charindex('Eaten by:',@string) + LEN('Eaten by:'), LEN(@string)), CHARINDEX(' ', SUBSTRING(@string,charindex('Eaten by:',@string) + LEN('Eaten by:'), LEN(@string)), 2)+1)))) AS Name;

Result:

Name
Bloggs, Joe

Fiddle here.

Note: It goes without saying, but I'll mention it anyways: you should consider upgrading your SQL Server to the latest version. Besides new features (like TRIM()), there are also security vulnerabilities in the older versions that can be exploited amongst a variety of other reasons.

UPDATE:

I now realize the end of your string is not a whitespace but rather a Line Feed LF represented by CHAR(10) ASCII here.

I've also added a subquery to get the string value for both middle and end of string scenarios with a CASE statement to check for a blank value; alternatively, you can use a CTE (see fiddle).

SELECT 
  CASE WHEN LocationMiddle = '' THEN LocationEnd 
  ELSE LocationMiddle END AS Name
  FROM (
 SELECT 
    LTRIM(RTRIM(SUBSTRING(SUBSTRING(@string,charindex('Requested by:',@string) + LEN('Requested by:'), LEN(@string)), 1, CHARINDEX(CHAR(10), SUBSTRING(@string,charindex('Requested by:',@string) + LEN('Requested by:'), LEN(@string)), CHARINDEX(' ', SUBSTRING(@string,charindex('Requested by:',@string) + LEN('Requested by:'), LEN(@string)), 2)+1)))) AS LocationMiddle,
    LTRIM(RTRIM(SUBSTRING(@string,CHARINDEX('Requested by:',@string)+LEN('Requested by:'),LEN(@string)))) AS LocationEnd
  ) A;

Fiddle here.