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) |
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 |