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?
11
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