SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2016 (SP3-OD) (KB5006943) - 13.0.6404.1 (X64) 
 Oct 18 2021 09:37:01 
 Copyright (c) Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



/* Below is your original string "Eaten by:"*/
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;


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;


Name


Bloggs, Joe


/* Below is your newly provided string "Requested by:"*/
DECLARE @string NVARCHAR (4000)
SET @string = '----- CCDDG ------ Requested Start Date/Time: 24-Oct-2022 11:23:00 Future Order: N Clinical Information: TFE and FEMRT for BABN AAD Bleep/Telephone Number: . Schedule Indicator: N Known Contrast Allergy: No Investigation Category: Transport: Carried Requested Date: Not Applicable TAD HGR: 30 or more Infection risk: None Requested by: Smith, John Location of care : xxx Th xxx xxxx, Room : Spare, Bed : YYY'
SELECT LTRIM(RTRIM(SUBSTRING(SUBSTRING(@string,charindex('Requested by:',@string) + LEN('Requested by:'), LEN(@string)), 1, CHARINDEX(' ', 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 Name;


Name


Smith, John


DECLARE @string NVARCHAR (4000)
SET @string = '----- CAAAG ------
Requested Start Date/Time: 24-Oct-2022 11:23:00
Future Order: N
Clinical Information: TFE and FEMRT for BABN AAD
Bleep/Telephone Number: .
Schedule Indicator: N
Known Contrast Allergy: No
Investigation Category: 
Transport: Carried
Requested Date: Not Applicable
TAD HGR: 30 or more
Infection risk: None
Requested by: Smith, John';

WITH CTE AS (
  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
)
SELECT 
  CASE WHEN LocationMiddle = '' THEN LocationEnd 
  ELSE LocationMiddle END AS Name
FROM CTE


Name


Smith, John


DECLARE @string NVARCHAR (4000)
SET @string = '----- CAAAG ------
Requested Start Date/Time: 24-Oct-2022 11:23:00
Future Order: N
Clinical Information: TFE and FEMRT for BABN AAD
Bleep/Telephone Number: .
Schedule Indicator: N
Known Contrast Allergy: No
Investigation Category: 
Transport: Carried
Requested Date: Not Applicable
TAD HGR: 30 or more
Infection risk: None
Requested by: Smith, John
Location of care : xxx Th xxx xxxx
Room : Spare
Bed : YYY';

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


Name


Smith, John