How to retrieve value between two similar chars

Question

I have following sample data and the query I have tried so far -

DECLARE @X TABLE (TAG VARCHAR(MAX))

INSERT INTO @X 
VALUES ('59D:$%$%$%$%^MESSAGE1^$%$%$%$%'),
       ('59D:--^MESSAGE2^$%$%$%$%')

SELECT * 
FROM @X

SELECT SUBSTRING(TAG,CHARINDEX('^',TAG)+1,LEN(TAG)-CHARINDEX('^',TAG))
FROM @X;

I want to select everything between ^. So in this case, my output should be MESSAGE1 and MESSAGE2

Answer 1

select 
substring(tag,charindex('^',tag)+1, len(tag) - charindex('^',reverse(tag)) - charindex('^',tag))
from @x

You might also want to exclude cases when there are less than 2 ^ in the string. In that case, you should add a where clause

WHERE len(tag) - len(replace(tag,'^','')) >= 2