How to find records not containing certain words

[ilyad]

Hi everybody,

I'm not sure if this problem has a SQL solution.

Suppose, I have a table called Parts with PartNum and Description fields. I'd like to select all records which do not contain certain words in the description field. The list of words is comma-delimited passed to SP, e.g.

Word1,Word2,Word3

Thanks in advance for your assistance.

 

[ptheriault]

Have you written anything yet? Can you post it if you have?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ilyad]

No, I haven't. I haven't yet installed SQL Server on this computer, will try to install SQL Express soon.

 

[AlexCuse]

Ilyad - check out this FAQs:

faq183-5207

This will show you how to split up the comma-separated list into a table variable.

You then could do something like this (using that function) to return everything that is not 'LIKE' any of the words in your comma separated string:

[COLOR=green]--set up test data
[/color][COLOR=blue]declare[/color] @t [COLOR=blue]table[/color] (descr [COLOR=blue]varchar[/color](1000))

[COLOR=blue]insert[/color] @t
[COLOR=blue]select[/color] [COLOR=red]'Alex'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'Ilyad'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'Hi Ilyad'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'its Alex'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'I think Ilyad has question'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'Steve'[/color]


[COLOR=green]--set up your parameter
[/color][COLOR=blue]declare[/color] @commaseplist [COLOR=blue]varchar[/color](100)
[COLOR=blue]set[/color] @commaseplist = [COLOR=red]'Ilyad, Steve'[/color]

[COLOR=green]--query to return non-matches
[/color][COLOR=blue]select[/color] a.descr 
[COLOR=blue]from[/color] @t a
[COLOR=blue]cross[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] [COLOR=blue]Value[/color] [COLOR=blue]from[/color] dbo.Split(@commaseplist, [COLOR=red]','[/color])
) b
[COLOR=blue]where[/color] a.descr not like [COLOR=red]'%'[/color] + b.Value + [COLOR=red]'%'[/color]
[COLOR=blue]group[/color] [COLOR=blue]by[/color] a.descr
[COLOR=blue]having[/color] [COLOR=#FF00FF]count[/color](a.descr) = 
([COLOR=blue]select[/color] [COLOR=#FF00FF]count[/color]([COLOR=blue]Value[/color]) [COLOR=blue]from[/color] dbo.Split(@commaseplist, [COLOR=red]','[/color]))

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Are you looking for something like this?

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](id [COLOR=blue]Int[/color], Words [COLOR=blue]VarChar[/color](8000))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](1, [COLOR=red]'Word1,Word2,Word3'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2, [COLOR=red]'Word2,Word3'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](3, [COLOR=red]'Word1,Word3'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](4, [COLOR=red]'Word1,Word2'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](5, [COLOR=red]'Word1,Word3'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](6, [COLOR=red]'Word1'[/color])

[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color]   @Temp 
[COLOR=blue]Where[/color]  [COLOR=#FF00FF]PatIndex[/color]([COLOR=red]'%,Word1,%'[/color], [COLOR=red]','[/color] + Words + [COLOR=red]','[/color]) = 0

The performance on this won't be too good if you have a really large table. Still, it's worth a try.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ilyad]

Hi Alex,

Yes, I know how to split the comma-delimited list into the table variable (learned here about 4 years ago fn_split function, then saw similar implementaions, one is here

http://www.berezniker.com/display/SQL/Alines+UDF+-+String+Split)

I need some time to digest this solution. I'm thinking that cross-join is going to be time consuming. May be this problem can be solved with CTE?

I guess rather than guessing, I really need to install SQL Server Express here.

 

[ilyad]

BTW, the link in option 2 doesn't work. Did it lead to fn_split?

 

[AlexCuse]

Yes, if you already have a split function no need. The link worked for me though. Weird...

I don't know that CTE would really be helpful in this scenario, but I am by no means an expert on that subject.


How big is your table, and how big are your lists of words passing in? That will determine how time consuming it is.

One thing you can do to improve speed is set 'Value' (or whatever the column returned by your particular split function is called) to the primary key (in the table variable created in your function). An index on the 'description' column may be helpful as well.

Good Luck,

Alex




Ignorance of certain subjects is a great part of wisdom

 

[ilyad]

The lists are not big, 5-6 words. BTW, it's not even my problem, I just read about this problem on another forum, there we solved it in the client's code, but I thought I'd ask how is it possible to do it on the server with T-SQL.

I'm trying to install SQL Express right now, but first it required .NET framework 2.0, so I started from there...

 

[bborissov]

ilyad,
Better buy SQL Server developer edition. It costs about 60$ but you will have a full power of SQL Server and all tools with it, like SSMS (full not Express) or SQL Profiler or ...

Even if your boss can't spend such amount of money for this you could buy it for yourself just to play, learn and be better.
If your firm is MSDN subscriber you could download it for free from MS MSDN site.

I am glad to see you here.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ilyad]

I think you can have it all for free including tools. I'm just installing SQL Express (first had to install Net Framework 2.0), so I'm going to play with these things more now.

Do you think I need to install replication?

BTW, any ideas on the thread's question? That's for UT Thread #1220224

 

[bborissov]

I think this is a DBA job not a programmer's one
Of course if you want to learn it install it.
About the tools - I played with SSMS Express and trust me there is nothing to compare with SSMS full. Of course if you find a free tools in the iNet that could do what SSMS could do there is no need to pay for it.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ilyad]

Too bad I already installed it. BTW, am I right choosing Windows authentication (hard word) mode? I think I left everything as default settings...

 

[bborissov]

No matter that you installed SQL Express version.
If you could have SQL Developer you could install only WorkStation tools and they would work with that instance of the SQL Server.
This is like you install MSDE on your computer and after that you install Enterprise Manager, Query Profiler and SQL Prfiler after that from some other version of SQL Server.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP