Thanks for the reply with good code. Sorry I took long getting back but I went back to the business person and asked for these requirements in the original query and I think I simplified it much more. I am getting an erro msg on "as ShortName on Line 13. But I think this will give me what i want.

Data:

clientvndname(fieldname)

SPECIALITES PRODAL 1975

ABUNDANCE MARKETING INC

ACH FOOD COMPANIES C/O 910040

ACTION LIGHTING

AON REED STENHOUSE INC

LEAHY ORCHARDS INC.

AQPP

CANADIAN SPRINGS

ARLA FOODS INC

HILLMANS TRANSFER CO LTD

Results desired:

SPECIALITES PRODAL 1975

ABUNDANCE MARKETING

ACH FOOD COMPANIES C/O 910040

ACTION LIGHTING

AON REED STENHOUSE

LEAHY ORCHARDS

AQPP

CANADIAN SPRINGS

ARLA FOODS

HILLMANS TRANSFER

;With ClientDomains_CTE3

AS

(

SELECT ClientDomains.DomainType

, ClientDomains.ClientVndNbr

, ClientDomains.ClientVndName

, LEFT(ClientVndName

, RTRIM(REPLACE(

REPLACE(

REPLACE(

REPLACE(ClientVndName, ' CO ', ' ')

,' INC ', ' ')

,' INC.', ' ')

,' LTD ', ' ')) as ShortName

FROM ClientDomains

WHERE ClientDomains.DomainType = 'CltDom'

AND PATINDEX('% INC %',DomainName) > 0 OR PATINDEX('% CO %',DomainName) > 0 OR PATINDEX('% LTD %',DomainName) > 0 OR PATINDEX('% CO.',DomainName) > 0

AND (ClientDomains.CorpVndNbr = 0 OR ClientDomains.CorpVndNbr IS NULL)

)

Select * From ClientDomains_CTE3