String between 2 Strings

Question

I am currently using the function listed below to find the text between 2 strings, my issue is where nothing is found it just returns an error and I wont to return either a space or Null, can anyone offer any advice on how to alter it please.?

CREATE FUNCTION [dbo].[udf_GetStringBetween2Strings] 
(
    @String varchar(max), 
    @FirstString varchar(256), 
    @SecondString varchar(256)
)
RETURNS VARCHAR(max) 
AS
BEGIN

    DECLARE @FirstPosition int,  @SecondPosition int
    SET @FirstPosition = CHARINDEX(@FirstString,@String) + LEN(@FirstString)
    SET @SecondPosition = CHARINDEX(@SecondString,@String) 
    RETURN (SELECT SUBSTRING(@String, @FirstPosition, @SecondPosition - @FirstPosition))

END

Answer 1

The most direct way is to check the result of charindex (it returns a 0 when no match is found). e.g.

DECLARE @FirstPosition int,  @SecondPosition int
SET @FirstPosition = CHARINDEX(@FirstString,@String) 
if @FirstPosition = 0 return 'Not found'
set @FirstPosition += LEN(@FirstString)
SET @SecondPosition = CHARINDEX(@SecondString,@String, @FirstPosition) 
if @SecondPosition = 0 return 'Not found'   
return SUBSTRING(@String, @FirstPosition, @SecondPosition - @FirstPosition)