I need to write a stored procedure which takes response from API and save values in local DB.For example, I have a string like this “Image is Non-compliant nFace Covered nIncorrect Lighting n”. I need to extract the text between n character like Face Covered and Incorrect Lighting. How to write a sql query to extract the text and the string length may varies.

I tried with
SELECT SUBSTRING(‘Image is Non-compliant nFace Covered nIncorrect Lighting n’,
CHARINDEX(‘n’,’Image is Non-compliant nFace Covered nIncorrect Lighting n’)+2,
CHARINDEX(‘n’,’Image is Non-compliant nFace Covered nIncorrect Lighting n’,CHARINDEX(‘n’,’Image is Non-compliant nFace Covered nIncorrect Lighting n’)+1) –
CHARINDEX(‘n’,’Image is Non-compliant nFace Covered nIncorrect Lighting n’)-2) as My_String.

But the output I’m getting only Face Covered.
But I need to extract whatever the text which is between n dynaically