Error for Invalid length parameter passed to SUBSTRING function

I have a table called tblPeople and this is created as shown below and filled with some data:

CREATE TABLE tblPeople 
(   
    LongName NVARCHAR(1024) NOT NULL,
)

INSERT INTO tblPeople (LongName)
VALUES ('OrgHarvest Inc (Home Shopping Latino Inc prior to 07/2018)'),
       ('Unknown Organisation');

Then, I have a query which causes an error:

SELECT 
    myValue = LTRIM(RTRIM(SUBSTRING(p.LongName, CHARINDEX('(', p.LongName) + 1, 
    (CHARINDEX('prior to', p.LongName) - CHARINDEX('(', p.LongName)) - 1)))
FROM 
    tblPeople p (NOLOCK)

I get this error:

Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

I've tried few things but couldn't work out. Can you advise if I'm doing something wrong. The error is with the row "Unknown Organisation"

Do I have a condition or what to do here?

Solution

As you clearly understand now, the problem is the fact that the length parameter you pass to the substring method is a negative number, in case the longName doesn't contain the prior to substring (or in case it's before the first ( char in the string).

This can be solved using a condition - using case or iif, or it can be solved using a combination of nullif, abs, isnull and len.

In both examples I'm using cross apply so that I only have to write each charindex call once. IMO, This makes the code much more readable

Using a condition:

SELECT LongName  
  ,myValue = IIF(PriorToIndex > OpenParenthesisIndex+1, 
      LTRIM(RTRIM(
      SUBSTRING(
          LongName, 
          OpenParenthesisIndex,
          (PriorToIndex - OpenParenthesisIndex - 1)
      )
  )), LongName)
  
FROM tblPeople  
CROSS APPLY (SELECT CHARINDEX('prior to', LongName) As PriorToIndex) As pti
CROSS APPLY (SELECT CHARINDEX('(', LongName) As OpenParenthesisIndex) As osi

Using NullIf/IsNull:

SELECT LongName  
  ,myValue = LTRIM(RTRIM(
      SUBSTRING(
          LongName, 
          OpenParenthesisIndex,
          ISNULL(NULLIF(Length, -ABS(Length)), LEN(LongName))
      )
  ))
  
FROM tblPeople  
CROSS APPLY (SELECT CHARINDEX('prior to', LongName) As PriorToIndex) As pti
CROSS APPLY (SELECT CHARINDEX('(', LongName) As OpenParenthesisIndex) As osi
CROSS APPLY (SELECT PriorToIndex - OpenParenthesisIndex - 1 As Length) As ssl

You can see a live demo on db<>fiddle