SYNTAX help, correct way to use LIKE in statement

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

General SQL Server Forums

New to SQL Server Programming

SYNTAX help, correct way to use LIKE in statement

Author

Topic

neyu
Starting Member

3 Posts

Posted - 2007-02-27 : 10:59:21

Hi, I'm new to SQL and was wondering if there was an easier way to filter data.

I have two tables -

The first table called Names of Companies has a column named: NAMES

NAMES
XYZ Company
ABC Limited Liability Company
ZZZ Corporation
KKK Inc.
ABC Inc.

The second table called Keywords has a column named: WORDS

WORDS
Company
Limited

I want to search for all NAMES that contain the WORDS in some form

The results should be:

NAMES
XYZ Company
ABC Limited Liability Company

Technically, I can get the results I want by manually typing into the SQL statement all the words that appear in the WORDS column.

SELECT *
FROM [Names of Companies]
WHERE [Names of Companies].Names Like "*Company*" Or ([Names of Companies].Names) Like "*limited*"));

But is there a way that I reference the table Keyword instead of typing into the query statement all the words that appear in the column WORD? I have a lot of words to search for.

Can anyone recommend a better way to do this?

Thanks for all your help!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-27 : 11:20:19

First of all, you must use % for wildcards not *, and single quotes, not double quotes around strings in T-SQL.

So your query will become

SELECT *
FROM [Names of Companies]
WHERE [Names of Companies].Names Like '%Company%' Or [Names of Companies].Names Like '%limited%';

Now to use all the words from your other table, read them into a dynamic query like this:


declare @search varchar(8000)
select @search = coalesce(@search + ' or Names like ''%' + Word + '%''', 'Names like ''%' + Word + '%''') 
from Words

exec('select * from [Names of Companies] where ' + @search)

Depending on how much data you have, this query might be very slow because an index cannot be used.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-27 : 11:27:34

No dynamic sql needed.

select distinct Companies.*
from Companies
inner join Words on Companies.Name like '%' + Words.Word + '%'

More info on this kind of stuff, including a LIKE expression that does a decent job of ensuring that you only return full words, not letters that match that are within a word, and some other ideas here:

http://weblogs.sqlteam.com/jeffs/archive/2004/11/02/2460.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

neyu
Starting Member

3 Posts

Posted - 2007-02-27 : 11:55:03

Thanks so much for your help! I knew there was a much simpler way to do this. I just didn't know the correct syntax. Awesome!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-28 : 11:11:02

quote:
No dynamic sql needed.

While it's true that dynamic SQL is not necessary, in this case you should get much better performance from the dynamic SQL because it just does one simple scan on the table, it doesn't have to process that very inefficient join (it's unavoidably inefficient because no indexing can be used).

neyu
Starting Member

3 Posts

Posted - 2007-02-28 : 12:30:46

Do you mean inefficient as in the time required to process would be longer or that the results would not be as precise?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-28 : 13:57:44

Same results, just slower and more load on the server. If you have a small amount of data then it makes no difference.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-28 : 14:09:16

snSQL --

that is a dangerous pattern to get into, dynamically creating sql statements when you don't need to in order to get better performance in certain cases. Performance is important, but security and maintainability are just as crucial and many times much more so than performance (esp. when the performance gain is a few milliseconds here and there).

Taking that to the next level, would you recommend that someone writes:

select a.*
from a
inner join b on a.id = b.id

using the same pattern? or:

select a.*, b.*
from a
inner join b on a.id =b.id
where b.status = 1

the same way? (i.e., first get a list of all b.id values that have a status of 1, build an IN() clause with that, and then use that instead of "WHERE b.status=1"). I sure hope not!

In theory, many SQL statements can be "rewritten" in that manner, by "peeking" at the data and dynamically creating long boolean expressions with all of that data embedded in it to "avoid" JOINS. I would strongly recommend against this unless there is no other choice and the performance benefit you get from this (which will vary according to the data -- sometimes it is better, sometimes worse) is crucial and outweighs the cost of abandoning best practices. What happens when your list of values results in a string > 8000 chars? What happens if there are quotation marks in the data? And so on ..... Avoiding dynamic sql means you never have to worry about any of those things.

- Jeff
http://weblogs.sqlteam.com/JeffS

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-28 : 15:28:13

Jeff, I agree with you 100% and would absolutely not use this as a general practice. This is a specific example though because the LIKE operator is not ideal as the basis for a JOIN.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources