Get string between a 2 strings-CodePudding

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

CodePudding user response：

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;