Subtraction of CHARINDEX in SUBSTRING is not working

Question

I have a value that I need to break down in column Name: AB: ABC-ABCDE I need the middle part which is ABC. I am using SUBSTRING and CHARINDEX to accomplish this but I am getting an error:

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

This happens when I subtract CHARINDEX to get the last value in SUBSTRING. Code:

SELECT PRODUCT = (SUBSTRING(Name, CHARINDEX(' ',Name)+1,CHARINDEX('-',Name)-(CHARINDEX(' ',Name)+1)))
FROM A

What am I doing wrong?

UPDATE: There is another value in the table like: 'ABC-ABC: ABCDEFG-ABCDEF GH'. This gives negative value, hence why the error. Result should be ABCDEFG

Answer 1

Another option is to "force" the NULL with a NullIf()

Example

... NullIf(CHARINDEX(' ',Name),0) + 1 ...

... NullIf(CHARINDEX('-',Name),0) ...

**

EDIT- Requested Update

**

Declare @YourTable table (Name varchar(50))
Insert Into @YourTable values
( 'AB: ABC-ABCDE')
,('ABC-ABC: ABCDEFG-ABCDEF GH')

Select A.*
      ,ltrim(rtrim(left(substring(Name,charindex(':',Name+':')+1,len(Name))
           ,charindex('-',substring(Name,charindex(':',Name+':')+1,len(Name))+'-') -1
           )))
 From  @YourTable A

Returns

Name                          (No column name)
AB: ABC-ABCDE                 ABC
ABC-ABC: ABCDEFG-ABCDEF GH    ABCDEFG