Sql-server – Remove string after first and second specific character in a string

sql servert-sql

I have some text in a field like below;

Confirmed Diagnosis 
================
Stroke || Entered: 19-02-2019 01:36:39 || Notes: 

Suspected Diagnosis
================
Transient ischaemic attack || Notes: Identified

I would like to extract any text after Confirmed Diagnosis ================ into a new field, and any text after Suspected Diagnosis ================ into another field.

Thank you all.

Best Answer

For a SQL solution, try something like this. It sounded like the section of text you wanted.

-- the same logic will work against a column in a query too.
if object_id('tempdb..#textThing','U') is not null
    begin
        drop table #textThing;
    end;
create table #textThing (
     id int identity(1,1) not null primary key
    ,text_stuff text
    ,varchar_stuff varchar(max)
);
insert into #textThing (text_stuff, varchar_stuff)
select 'Confirmed Diagnosis 
================
Stroke || Entered: 19-02-2019 01:36:39 || Notes: brain fell out, recommend duct tape

Suspected Diagnosis
================
Transient ischaemic attack || Notes: Identified || suspect brain loose'
,'Confirmed Diagnosis 
================
Stroke || Entered: 19-02-2019 01:36:39 || Notes: jose fell of my left eye onto my right pinky toe.

Suspected Diagnosis
================
Transient ischaemic attack || Notes: Identified || there''s something missing from jose''s eye '

;with initialText as ( -- to clean, reducing line ends to only single char(13) (carriage return) stripping off char(10) (line feed), this only applies to the info in memory and will not affect the column in the table.
select 
     replace(replace(replace(convert(varchar(max),text_stuff),char(10),''),char(13),''),'Confirmed Diagnosis ================','') cleanstext
    ,replace(replace(replace(varchar_stuff,char(10),''),char(13),''),'Confirmed Diagnosis ================','') cleanvarchar
    ,reverse(replace(replace(convert(varchar(max),text_stuff),char(10),''),char(13),''))  cleanstext1
    ,reverse(replace(replace(varchar_stuff,char(10),''),char(13),'')) cleanvarchar1
from #textThing
)
select 
     left(cleanstext,charindex('Suspected Diagnosis================',cleanstext) -1) textConfirmedDiagnosis
    ,left(cleanvarchar,charindex('Suspected Diagnosis================',cleanvarchar) -1) varcharConfirmedDiagnosis
    ,right(cleanstext,charindex(reverse('Suspected Diagnosis================'),cleanstext1) -1) textSuspectedDiagnosis
    ,right(cleanvarchar,charindex(reverse('Suspected Diagnosis================'),cleanvarchar1) -1) varcharSuspectedDiagnosis
from initialText

Results were 

[textConfirmedDiagnosis]
Stroke || Entered: 19-02-2019 01:36:39 || Notes: brain fell out, recommend duct tape

and 

[varcharConfirmedDiagnosis]
Stroke || Entered: 19-02-2019 01:36:39 || Notes: jose fell of my left eye onto my right pinky toe.

If your results are different then you got some extra characters in your string that I do not have in mine.

I may have written it, but I have no idea how it works, logic dictates I'd need to reverse() it one more time to get the right value, but hey, who am I to argue with a working script, tested on 2014, 2017 and 2017 on centos

Related Question