Since it's SQL 2000, the best bet will probably be a UDF that uses a Numbers or Tally table to split it apart into rows, based on the commas. That way, you can take out the part you want to if it's an exact match.
Would look something like this, but with your Numbers/Tally table, and columns instead of variables:
DECLARE @String VARCHAR(100), @Exclusion VARCHAR(100);
SELECT @String = 'number street, town, county', @Exclusion = 'town';
SELECT SUBSTRING(@String+',', number,
CHARINDEX(',', @String+',', number) - number)
WHERE number <= LEN(REPLACE(@String,' ','|'))
AND SUBSTRING(',' + @String,
LEN(REPLACE(',',' ','|'))) = ','
AND LTRIM(SUBSTRING(@String+',', number,
CHARINDEX(',', @String+',', number) - number)) != @Exclusion;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon