In the absence of test data here is my spin on it

DECLARE @Client_Domains TABLE

(

Client_VndName NVARCHAR(100) ,

Client_Type NVARCHAR(100) ,

CorpVndNbr INT

)

INSERT INTO @Client_Domains

( Client_VndName, Client_Type, CorpVndNbr )

VALUES ( N'Test 1 CO Lala', 'CltDom', 0 ),

( N'Test 2 CO Lala', 'CltDom', NULL ),

( N'Test 3 CO Lala', 'ABCDom', 1 ),

( N'Test 4 CO Lala', 'ABCDom', NULL ),

( N'Test 5 INC Lala', 'CltDom', NULL ),

( N'Test 6 Lala', 'CltDom', 1 ),

( N'Test 7 Lala', 'CltDom', NULL )

SELECT Client_VndName ,

CASE WHEN CHARINDEX(' CO', Client_VndName) > 0

AND SUBSTRING(Client_VndName,

CHARINDEX(' CO', Client_VndName), 4) = ' CO '

THEN CHARINDEX(' CO', Client_VndName)

WHEN CHARINDEX(' INC', Client_VndName) > 0

THEN CHARINDEX(' INC', Client_VndName)

WHEN CHARINDEX(' LLC', Client_VndName) > 0

THEN CHARINDEX(' LLC', Client_VndName)

ELSE 0

END AS EndPt ,

SUBSTRING(Client_VndName, 1,

CASE WHEN CHARINDEX(' CO', Client_VndName) > 0

AND SUBSTRING(Client_VndName,

CHARINDEX(' CO', Client_VndName), 4) = ' CO '

THEN CHARINDEX(' CO', Client_VndName)

WHEN CHARINDEX(' INC', Client_VndName) > 0

THEN CHARINDEX(' INC', Client_VndName)

WHEN CHARINDEX(' LLC', Client_VndName) > 0

THEN CHARINDEX(' LLC', Client_VndName)

ELSE 0

END) AS ShortName

FROM @Client_Domains

WHERE ( Client_Type = 'CltDom' )

AND ( CorpVndNbr = 0

OR CorpVndNbr IS NULL

)

AND CASE WHEN CHARINDEX(' CO', Client_VndName) > 0

AND SUBSTRING(Client_VndName,

CHARINDEX(' CO', Client_VndName), 4) = ' CO '

THEN 1

WHEN CHARINDEX(' INC', Client_VndName) > 0 THEN 1

WHEN CHARINDEX(' LLC', Client_VndName) > 0 THEN 1

ELSE 0

END = 1

And the final output

Client_VndNameEndPtShortName

Test 1 CO Lala7Test 1

Test 2 CO Lala7Test 2

Test 5 INC Lala7Test 5