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

Question

Hi,

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

*/

Answer 1

Probably it is easier to reconsider the implementation:

drop table if exists #TMP_ERS

create table #TMP_ERS
(
    Patient_Name VARCHAR(100)
)

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)

Answer 2

Just to be clear.

Your code uses CHARINDEX to search for a string. If that string is not found, it returns 0. You then use 0-1 = -1 which is invalid as a start or a length for substring.