exclude records 'like' a list passed in

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

exclude records 'like' a list passed in

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2009-05-27 : 07:04:57

Hi,

Not sure if I need an actual solution on this one - maybe more a little guidance... who am I trying to kid!

We've got a casework 'system' based on SQL 2000.

A while ago I wrote a sproc to randomly pull out a percentage of cases completed within a period that a report then uses to generate feedback questionnaires. Which was fine.

Today, a user has come to me and they want to exclude certain cases from the sample based on whether a word appears in the casenotes table.

For instance, say the casenotes table looks like this:

create table #casenotes
(
caseid int identity(1, 1),
note varchar(1024)
)

insert #casenotes 
select 'member is dead' union
select 'member is deceased' union
select 'member is not alive any more' union
select 'member is in the rudest of health'

I need to exclude anything where the note field indicates that the member might not want to take part in a survey.

Because they're dead.

So I could do something like this:


select * from #casenotes
where note not like '%dead%' 
and note not like '%deceased%'
and note not like '%is not alive any more%'

which returns only the record that I want, but obviously there's no way of guaranteeing that the things i've identified above are the only ways that death is described in the table. The users could literally have typed anything in.

I know what will happen. Maybe next month, maybe the month after, there will be a word or phrase in the notes that hasn't been accounted for and I'll have to add the new word into the sproc again, so to prevent this I want to pass in a list of, or maybe use a table of words and phrases to exclude, that the users can update themselves.

Any starters / pointers on how I can do this? Or just give me a bit of direction? I've been kicking it around most of the morning, and haven't really got anywhere.

Cheers,

Yonabout

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 07:14:48

Make an exclusion table and add the exclusion words there.

declare @casenotes table
	(
		caseid int identity(1, 1),
		note varchar(1024)
	)

insert	@casenotes 
select	'member is dead' union all
select	'member is deceased' union all
select	'member is not alive any more' union all
select	'member is in the rudest of health'

declare @exclusion table
	(
		word varchar(1024)
	)

insert	@exclusion
select	'%dead%' union all
select	'%deceased%' union all
select	'%is not alive any more%'

select	cn.*
from	@casenotes AS cn
where	not exists (select * from @exclusion where cn.note like '%' + word + '%')