Get string between a 2 strings

September 6, 2022

I am creating a query to get the answer between 2 questions in string column, so basically the sample data looks like:

1. Over the past two weeks, have you felt down, depressed or hopeless? NO 2.  Over the past two weeks, have you felt little interest or pleasure in doing things?

so I need to get only the "NO" answer, the format is always the same

>Solution :

The text has not formatted well. If you can add a simple character between question and answers there will be a simple solution:
See my sample:

DECLARE @String NVARCHAR(4000) = N'1. Over the past two weeks, have you felt down, depressed or hopeless? NO ;2.  Over the past two weeks, have you felt little interest or pleasure in doing things? YES';

SELECT SUBSTRING(ss.value, 0, CHARINDEX('?', ss.value) + 1) AS Question, SUBSTRING(ss.value, CHARINDEX('?', ss.value) + 1, LEN(ss.value)) Answer
FROM STRING_SPLIT(@String, ';') AS ss;