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 |