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
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;