quote:

---

Originally posted by robvolk

Change all the ORs to AND.

---

quote:

---

Originally posted by madhivanan

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

---

create table #yak (YakName varchar(50))

insert into #yak (YakName)
select 
	'DARRELL S OVERHEAD DOOR SERVICE LLC'
union all select 'CWM REMODELING LLC'
union all select 'CURRAN ACCOUNTING SERVICES LLC'
union all select 'CMP MARINE SALES LLC'
union all select 'CEYLAN PAINTING LLC'
union all select 'BERKE YOUNG INTERNATIONAL LLC'
union all select 'ARNISDALE INV LLC'
union all select '4WARD THINK CONSULTING LLC'
union all select 'WINSTON & ALLIED CORP'
union all select 'TORRES ROOFING AND SIDING LLC'
union all select 'STEVENS & JOHNSON LLC'
union all select 'STEVE PAUL LLC'
union all select 'SANDERSONS PAINTING LLC'
union all select 'S & S STUDENT HOUSING LLC'
union all select 'ROBERT JENSEN RESTORATION CORPORATION'
union all select 'RNOBLE LLC'
union all select 'WARFIELD PLUMBING & HEATING INC'
union all select 'PHILIP M COHN'
union all select 'REINHOLD F TRAUB'
union all select 'SEAN MENDEZ CATLIN'
union all select 'WILLIAM B BLOOM'
union all select 'SCOTT E LANDRY'
union all select 'HOLSBERG, WILLIAM A '


select 
	yakname 
from 
	#yak
WHERE 
	RIGHT(LTRIM(rtrim(YakName)),charindex (' ', reverse(LTRIM(rtrim(YakName))),1) -1) NOT LIKE '%LLC%'
AND RIGHT(LTRIM(rtrim(YakName)),charindex (' ', reverse(LTRIM(rtrim(YakName))),1) -1) NOT LIKE '%CORP%'
AND RIGHT(LTRIM(rtrim(YakName)),charindex (' ', reverse(LTRIM(rtrim(YakName))),1) -1) NOT LIKE '%INC%'

drop table #yak

CREATE TABLE #Test (Name VARCHAR(255))
INSERT INTO #Test 
SELECT 'DARRELL S OVERHEAD DOOR SERVICE LLC' UNION
SELECT 'CWM REMODELING LLC' UNION
SELECT 'CURRAN ACCOUNTING SERVICES LLC' UNION
SELECT 'CMP MARINE SALES LLC ' UNION
SELECT 'CEYLAN PAINTING LLC' UNION
SELECT 'BERKE YOUNG INTERNATIONAL LLC ' UNION
SELECT 'ARNISDALE INV LLC' UNION
SELECT '4WARD THINK CONSULTING LLC' UNION
SELECT 'WINSTON & ALLIED LLC' UNION
SELECT 'TORRES ROOFING AND SIDING LLC ' UNION
SELECT 'STEVENS & JOHNSON LLC' UNION
SELECT 'STEVE PAUL LLC' UNION
SELECT 'SANDERSONS PAINTING LLC' UNION
SELECT 'S & S STUDENT HOUSING LLC ' UNION
SELECT 'ROBERT JENSEN RESTORATION LLC' UNION
SELECT 'RNOBLE LLC ' UNION
SELECT 'WARFIELD PLUMBING & HEATING INC ' UNION
SELECT 'PHILIP M COHN ' UNION
SELECT 'REINHOLD F TRAUB ' UNION
SELECT 'SEAN MENDEZ CATLIN ' UNION
SELECT 'WILLIAM B BLOOM ' UNION
SELECT 'SCOTT E LANDRY ' UNION
SELECT 'HOLSBERG, WILLIAM A '

SELECT * 
FROM #Test
where (name not like '% LLC'
and NAME NOT LIKE '%LLC%'
and NAME NOT LIKE 'CORPORATION %'
and NAME NOT LIKE '% CORP %'
and NAME NOT LIKE '% INC'
and NAME NOT LIKE 'INC %'
and NAME NOT LIKE '% INC%')