I have the following error message

Msg 537, Level 16, State 2, Line 83 - Invalid length parameter passed to the LEFT or SUBSTRING function

Below are the steps to replicate using a number of different test cases.

The aim is to not get any error message should any element of Patient_Name NOT be supplied

create table #TMP_ERS
Patient_Name VARCHAR(100),  
PERSON_TITLE VARCHAR(20),   
PERSON_FAMILY_NAME VARCHAR(100),    
PERSON_GIVEN_NAME VARCHAR(100)  
INSERT INTO #TMP_ERS VALUES
('EVANS, Simon Kristian (Mr.)', 'Mr.', 'EVANS', 'Simon Kristian'),  --- Surname, Forename, Middlename, Title (with .)
('EVANS, Amy Louise (Mrs)', 'Mrs', 'EVANS', 'Amy Louise'),          --- Surname, Forename, Middlename, Title (without .)
('EVANS, Henry (Mr)',   'Mr', 'EVANS', 'Henry'),                    --- Surname, Forename, Title (without .)
('EVANS, John (Mr.)',   'Mr.', 'EVANS', 'John'),                    --- Surname, Forename, Title (with .)
('EVANS, Tara ()',  '', 'EVANS', 'Tara'),                           --- Surname, Forename, No Title 
('EVANS, (Mrs)',    'Mrs', 'EVANS', '')                             --- Surname, Title (without .) 
--TRUNCATE TABLE #TMP_ERS
SELECT 
CAST(SUBSTRING(#TMP_ERS.[Patient_Name], CHARINDEX('(', #TMP_ERS.[Patient_Name]) + 1, CHARINDEX(')', #TMP_ERS.[Patient_Name]) - CHARINDEX('(', #TMP_ERS.[Patient_Name]) - 1) AS VARCHAR(255)) AS [PERSON_TITLE]
,CAST(LEFT(#TMP_ERS.[Patient_Name], CHARINDEX(',', #TMP_ERS.[Patient_Name]) - 1) AS VARCHAR(255)) AS [PERSON_FAMILY_NAME]
,CAST(SUBSTRING(#TMP_ERS.[Patient_Name], CHARINDEX(',', #TMP_ERS.[Patient_Name]) + 1, CHARINDEX('(', #TMP_ERS.[Patient_Name]) - CHARINDEX(',', #TMP_ERS.[Patient_Name]) - 1) AS VARCHAR(255)) AS [PERSON_GIVEN_NAME] 
from #TMP_ERS
-- 2. What if an extract didn't hold a Title and the Brackets were NOT supplied, below is a test case
INSERT INTO #TMP_ERS VALUES
('TESTPATIENTSURNAME, TESTPATIENTFORENAME', '', 'TESTPATIENTSURNAME', ' TESTPATIENTFORENAME ')
SELECT 
CAST(SUBSTRING(#TMP_ERS.[Patient_Name], CHARINDEX('(', #TMP_ERS.[Patient_Name]) + 1, CHARINDEX(')', #TMP_ERS.[Patient_Name]) - CHARINDEX('(', #TMP_ERS.[Patient_Name]) - 1) AS VARCHAR(255)) AS [PERSON_TITLE]
,CAST(LEFT(#TMP_ERS.[Patient_Name], CHARINDEX(',', #TMP_ERS.[Patient_Name]) - 1) AS VARCHAR(255)) AS [PERSON_FAMILY_NAME]
,CAST(SUBSTRING(#TMP_ERS.[Patient_Name], CHARINDEX(',', #TMP_ERS.[Patient_Name]) + 1, CHARINDEX('(', #TMP_ERS.[Patient_Name]) - CHARINDEX(',', #TMP_ERS.[Patient_Name]) - 1) AS VARCHAR(255)) AS [PERSON_GIVEN_NAME] 
from #TMP_ERS
-- 3. I now get the following error message - Msg 537, Level 16, State 2, Line 83 - Invalid length parameter passed to the LEFT or SUBSTRING function. This means the current SQL can't handle the absence of the brackets in the PERSON_TITLE field?
The object is using the SOURCE data, in this case its the Patient_Name field which I then use to create seperately the other 3 fields (PERSON_TITLE, PERSON_FAMILY_NAME, PERSON_GIVEN_NAME). In essence splitting the Patient_Name
Surname is alwats following by a comma and ALWAYS populated, other name can be none or as many as provided. Title not always supplied but need to handle if brackets are supplied or not.
Missing elements of the patient name are handled.
forename missing
surname missing
title missing
middle name missing
combinations of the above
INSERT INTO #TMP_ERS VALUES
('EVANS, Simon Kristian (Mr.)' ),
('EVANS, Amy Louise (Mrs)' ),    
('EVANS, Henry (Mr)' ),    
('EVANS, John (Mr.)' ),    
('EVANS, Tara ()' ),    
('EVANS, (Mrs)' ),
('TESTPATIENTSURNAME, TESTPATIENTFORENAME' )
select * from #TMP_ERS
select 
    Patient_Name,
    isnull(case when a.c > 0 and a.lp > a.c and a.rp > a.lp then trim(substring(Patient_Name, a.lp + 1, a.rp - a.lp - 1)) end, '') as PERSON_TITLE,
    isnull(case when a.c > 0 then trim(substring(Patient_Name, 1, a.c - 1)) end, '') as PERSON_FAMILY_NAME,
    isnull(case 
        when a.c > 0 and a.lp > a.c then trim(substring(Patient_Name, a.c + 1, a.lp - a.c - 1)) 
        when a.c > 0 then trim(substring(Patient_Name, a.c + 1, len(Patient_Name))) end, '') as PERSON_GIVEN_NAME
from #TMP_ERS
cross apply (values (CHARINDEX(',', Patient_Name), CHARINDEX('(', Patient_Name), CHARINDEX(')', Patient_Name))) a(c, lp, rp)
												
Why thank you, had not though about this although I don't have TRIM in my version so used LTRIM(RTRIM( etc.  
I would like to understand what you've done for future reference if may ask. what do the letters represent, I'll also review what the CROSS APPLY is doing.  
Again huge thanks