Like vs Contains

Grant Quick

When building a query, what is the functional difference between the Like and Contains operators? Both can take wildcards, and both seem to produce the same results. Since Like requires wildcards, but Contains does not (although can accept them), under what circumstances should the Like operator be used instead of Contains?


Chher

Alan French

I would use Like instead of Contains if I was looking for something that I know must begin with a series of characters, not merely contain it. The query "Name like Alan%" is going to run much more quickly than "Name contains Alan" since RE can immediately disregard any value that doesn't begin with the word Alan.

Grant Quick

Alan French:

I would use Like instead of Contains if I was looking for something that I know must begin with a series of characters, not merely contain it. The query "Name like Alan%" is going to run much more quickly than "Name contains Alan" since RE can immediately disregard any value that doesn't begin with the word Alan.

Cheers Alan, would you not use the Begins With operator in that example though?

Alan French

Yes, good point and bad example!

Tim Stahl

Contains is usually used to compare one item, for example: "Smith" with a list of items: "Smith, Lane, Johnson, Carter"


Like is used to compare one item "Smith" with "Smi%". This will return not only Smith, but Smitty, Smithy, etc.


Does that help?


Thanks!

Grant Quick

Tim Stahl:

Contains is usually used to compare one item, for example: "Smith" with a list of items: "Smith, Lane, Johnson, Carter"


Like is used to compare one item "Smith" with "Smi%". This will return not only Smith, but Smitty, Smithy, etc.


Does that help?


Thanks!

Cheers Tim. To get the same result as the Like example above, I could just use Begins With "Smi" though right?

Tim Stahl

Yes! Absolutely. Like can also be used to find things in the middle of a word, for example: "%are%" could match to Care, or area.

Grant Quick

That's what Contains does!

Alan French

Perhaps Contains was added more recently to remove the need to use wildcards? I guess not everyone is familiar with how to use them.


Does anyone have the RODBA module and can test whether the query generates the same SQL code for both "contains Smith" and "like %Smith%"? I'm intrigued...

Grant Quick

Alan French:

Perhaps Contains was added more recently to remove the need to use wildcards? I guess not everyone is familiar with how to use them.


Does anyone have the RODBA module and can test whether the query generates the same SQL code for both "contains Smith" and "like %Smith%"? I'm intrigued...

That's what I was thinking. It would appear that Like and Not Like can replicate all the functionality of at least Begins With, Does Not Begin With, Contains and Does Not Contain, assuming appropriate use of wildcards. I just want to make sure I'm not missing something!

James Andrews

Yeah I have RODBA and took a look.  If you say first name contains Jam, the SQL it generates is:

WHERE [first_name] LIKE '%Jam%'


Like just translates whatever you put in there to SQL.  So if you say first name like Jam*, the SQL it generates is

WHERE [first_name] LIKE 'Jam%'


So yes, it'll probably be quicker if you use like and specify your wildcards, but only if you have the wildcard at the end of the string rather than the beginning, because if you say first name like *Jam*, it'll return the same SQL as if you say first name contains Jam.

Tim Stahl

You're good to go, that's correct! Like and Contains are really SQL database terms. When dealing with database folks like myself, those just make sense to me :-)

Grant Quick

Perfect, cheers James. Reckon I'll just use Like as I would in SQL just to avoid any ambiguity!

Grant Quick

Tim Stahl:

You're good to go, that's correct! Like and Contains are really SQL database terms. When dealing with database folks like myself, those just make sense to me :-)

From the RODBA extract it doesn't look like the the Contains operator is equivalent to the CONTAINS predicate in SQL though. It's just mapping it to LIKE with bounding wildcards. So it appears there's no functional difference between the two RE operators besides the requirement to include at least one wildcard when adding a Like, i.e. there's no functional or performance advantage to using Contains "badger" over Like "%badger%".

James Andrews

Grant Quick:


From the RODBA extract it doesn't look like the the Contains operator is equivalent to the CONTAINS predicate in SQL though. It's just mapping it to LIKE with bounding wildcards. So it appears there's no functional difference between the two RE operators besides the requirement to include at least one wildcard when adding a Like.

 

Yeah exactly.  Contains just cuts out a couple keystrokes for the user, is all.