2

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.

  • 4
    Operations like this need to be done at the application layer if possible and passed into SQL Server such that the database engine isn't being used for things like parsing text. C# and Java are much better suited for doing this type of work. – Tony Hinkle Mar 14 at 15:59
  • Thank you @TonyHinkle – aa888817 Mar 14 at 16:08
  • Also, there are a lot of details that would be needed to answer this 100% correctly. The first thing that comes to my mind is where to terminate the column data. Should the first column end when it runs into "Suspected Diagnosis"--that's dangerous, because the notes could include that text. Should it end when it encounters a line feed or carriage return? If yes, are you sure that all line feeds and carriage returns are stripped out of the notes before you get it? Lots of pitfalls here with taking output data and trying to parse it, either in SQL or in application. – Tony Hinkle Mar 14 at 16:22
1

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

  • Thanks Michael, the code works really well. Truly appreciated – aa888817 Mar 18 at 8:57
  • @aa888817: Just to clarify, because it looks like you were confused about the authorship of the post – this answer was merely edited by Michael Green (formatting and some such things) but it was JBJ who actually posted the solution. – Andriy M Mar 19 at 19:12
  • Thanks for the clarification Andriy, special thanks go to JBJ and Michael for formatting – aa888817 Mar 21 at 13:38
0

Another possible solution:

DROP TABLE IF EXISTS #temp 
CREATE TABLE #temp  ( YourField varchar(400), field2 varchar(400), field3 VARCHAR(400))

INSERT INTO #temp (YourField) VALUES ('
Confirmed Diagnosis 
================
Stroke || Entered: 19-02-2019 01:36:39 || Notes: 

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


Select 
YourField
,REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(YourField, P1.Pos +1,  P2.Pos - 1),'Confirmed Diagnosis ',''), '================',''), CHAR(13), ''), CHAR(10), '')  as field2
,REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(YourField, P2.Pos ,  LEN(YourField)),'Suspected Diagnosis',''),'================',''), CHAR(13), ''), CHAR(10), '')  as field3
from #temp
  cross apply (select (charindex('Confirmed Confirmed Diagnosis', YourField))) as P1(Pos)
  cross apply (select (charindex('Suspected Diagnosis', YourField, P1.Pos+1))) as P2(Pos)

Check the result here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.