0

I am retrieving the following values but it is not consistent. I need the values after the GESTATIONAL AGE:. Sometimes it picks up after sometimes it does not. The following are the values being returned. Sometimes it contains Gestational Age, sometimes is does not . I do not want the words Gestational Age. I just want 37w3d etc. 

 Gestational Age: 37w3d

  39w6d

  39w0d

  34w6d

  41w0d 

  Gestational Age: 39w5d

I have tried to 'tweak' the substr but it can't pick up the string consistently

I am using the following formula to pick up my values but it is not working on all of them 

select SUBSTR(a.note_text, 
       INSTR(a.note_text,'Gestational Age:')+17, 
       INSTR( 
           SUBSTR(a.note_text,INSTR(a.note_text,'Gestational Age:')+17) , 'd')+1 ) as GA
from 
table
Evan M
  • 2,573
  • 1
  • 31
  • 36
sharona
  • 1
  • 1
  • 1
  • 3
  • 1
    Hello, and welcome to Stack Overflow. For us to help you, it would be great if you could show us some sample data and the expected results. Otherwise, we're pretty much just guessing. – Neville Kuyt Sep 10 '19 at 14:25
  • The value of `a.note_text` always contains `Gestational Age:`? In that case you can simply use `SUBSTR(a.note_text, INSTR(a.note_text,'Gestational Age:')+17)` – Robert Kock Sep 10 '19 at 14:30

1 Answers1

3

Is it maybe as simple as using a REPLACE?

select REPLACE(a.note_text,'Gestational Age: ','') as GA
from 
table
HereGoes
  • 1,302
  • 1
  • 9
  • 14