Retrieve Specific Text from String where Search String Exists and Not Exists

I’ve got the following code. It returns the correct result for the first two test strings, where it should return the text between the two ****.

The 3rd string returns ” abcd two two ****” rather than ” abcd two two ” The 4th string returns “Invalid length parameter passed to the LEFT or SUBSTRING function.”

Any ideas?

WITH yourTable AS (
    SELECT '**** mary had a little lamb****' AS TestString UNION ALL
    SELECT '**** humpy dumpty had a great fall**** All the king''s horses and all the king''s men' UNION ALL
    SELECT 'bla **** abcd two two **** dfdfdfd' UNION ALL
    SELECT 'test'
)
SELECT
    TestString,
    SUBSTRING(TestString,
              CHARINDEX('****', TestString) + 4,
              CHARINDEX('****', TestString, CHARINDEX('****', TestString) + 1) - 5) AS contents
FROM yourTable;

Answer

The search terms are four asterisks. This approach uses CHARINDEX, once forwards to find the first set, and then with REVERSE to find the last occurrence.

;WITH yourTable AS (
    SELECT '**** mary had a little lamb****' AS TestString UNION ALL
    SELECT '**** humpy dumpty had a great fall**** All the king''s horses and all the king''s men' UNION ALL
    SELECT 'bla **** abcd two two **** dfdfdfd' UNION ALL
    SELECT 'test'
)
SELECT
    TestString,
    trim(case when cx.cndx=4 then TestString 
        else substring(TestString, cx.cndx, (cx.len_t-cx.cndx)-cx.rndx-2) end) MiddleString
FROM yourTable
     cross apply
     (select len(TestString) len_t,
             charindex('****', TestString)+4 cndx,
             charindex('****', reverse(TestString)) rndx) cx;

Output

TestString                              MiddleString
**** mary had a little lamb****         mary had a little lamb
**** humpy [shortened] fall**** All...  humpy dumpty had a great fall
bla **** abcd two two **** dfdfdfd      abcd two two 
test                                    test