ISOLATING A BLOCK OF TEXT FROM A LARGER BLOCK OF TEXT WITHIN 1 COLUMN

Database

ISOLATING A BLOCK OF TEXT FROM A LARGER BLOCK OF TEXT WITHIN 1 COLUMN

I'm a novice with SQL, but hoping someone can help.   It's for a cancer project, so you will be helping us save lives with your efforts.  Any assistance would be greatly appreciated.  

I really need to isolate the query to a specific block of text within a larger block of text.  The block of text I would like to do my case statement on looks something like this.  I would like to search the text only between "TITLE OF BLOCK" and "Signed By."  All the text is in one column called "Results" in a table called "Report."  If I see WORD2 AND WORD4 in any combination, then I'd like to count it as 1 (else 0) in a column called "WORDS2AND4" in a table called "ReportBrief."  

NOT INTERESTED IN ANY WORDS ABOVE THE LINE

---------

TITLE OF BLOCK

WORD1 WORD2 WORD3 WORD4 WORD5

Signed By

---------

NOT INTERESTED IN ANY WORDS BELOW THE LINE

2 REPLIES
Teradata Employee

Re: ISOLATING A BLOCK OF TEXT FROM A LARGER BLOCK OF TEXT WITHIN 1 COLUMN

The regexp_similar function should provide what you need.

select regexp_similar(results, '.*TITLE OF BLOCK.*(WORD2.*WORD4|WORD4.*WORD2).*Signed By.*', 'c') as words2and4
, results
from report

The third argument of regexp_similar can be 'c' (as shown) for case-sensitive matching, or 'i' for case-insensitive.

Re: ISOLATING A BLOCK OF TEXT FROM A LARGER BLOCK OF TEXT WITHIN 1 COLUMN

Thank you so much!  It works if there is only one word without spaces.  

1) If "WORD4" was instead "WORD 4" how would I deal with the space?  Even for "Signed By" if there is a space the result is 0 rather than 1.  

2) Also, how do you include NOT like?  For example, I'd like the below logic to result in 1, else 0.

(

If like WORD2 AND (like WORD4 but NOT like WORD3)

Or like WORD4 AND (like WORD2 but NOT like WORD3)

)

, then 1, else 0