SQL question, LIKE or '=' ?

Coders forum. Discuss techniques, swap code snippets, ask for help etc. relating to all forms of software development.
Post Reply
User avatar
gilesgoat
Sheepie Whore
Sheepie Whore
Posts: 27259
Joined: Wed Sep 03, 2003 7:54 am 0
Location: Monfalcone, Italy
Contact:

SQL question, LIKE or '=' ?

Post by gilesgoat »

This is a 'simple SQL question', let's say I have a DB with a column 'NAME' which is a VARCHAR(64).

I want to find all the names 'banana' but with a case INsensitive search so I want that 'banana','Banana','bAnana' and so on are treated the same.

So .. a quick test shows/tell that for example

Code: Select all

SELECT name from users where name = 'banana' 
does the trick.

But so also

Code: Select all

SELECT name from users where name LIKE 'banana'
What I read around seems to tell that while '=' skips trailing spaces and such LIKE does not.

So .. 'what's the best way' for this search ? Skipping white spaces would be also nice I mean I'd like if ' banana' and/or 'banana ' or ' banana ' would be treated the same.

I just don't get exactly 'what's LIKE for compared to =' ?
Giles the billy goat
"Baa"
User avatar
gravy
Sheepie Whore
Sheepie Whore
Posts: 19572
Joined: Wed May 01, 2002 1:28 pm 0
Location: The Other Place

Re: SQL question, LIKE or '=' ?

Post by gravy »

Try this:

SELECT name from users where LCASE(RTRIM(name)) = 'banana'

RTRIM strips trailing white spaces
LCASE lowers case
User avatar
Colonius
Baaaa!
Baaaa!
Posts: 787
Joined: Tue Dec 13, 2005 8:41 pm 0
Location: Wiltshire

SQL question, LIKE or '=' ?

Post by Colonius »

Gravy's code will do what you want. LIKE can have wildcards in, so for example:

Select * from staff where name LIKE 'John%Smith'

will find people with anything at all between John and Smith in their name. % matches a number of chars, _ matches a single char. That's the MS SQL way, can't speak for others.
I made my song a coat
Covered with embroideries
User avatar
Istar
Baaaaaa!
Baaaaaa!
Posts: 4869
Joined: Mon Apr 22, 2002 4:42 pm 0
Location: London, UK.

Re: SQL question, LIKE or '=' ?

Post by Istar »

Also, from a performance perspective, '=' will use an index on the search column if there is one, whereas 'LIKE' will do a slower table scan. It might use an index if there's no leading wildcard, but if your SQL is trying to be DB engine-independent then you can't assume the optimiser will make that distinction.
melkig
Posts: 9
Joined: Fri Oct 31, 2008 11:06 am 0

Re: SQL question, LIKE or '=' ?

Post by melkig »

"if your SQL is trying to be DB engine-independent then you can't assume the optimiser will make that distinction."

That's the main point. "=" might not do the same on another DB. LIKE should work the same no matter what.

The reason "=" strips any zeros is because the guy who wrote the DB engine told it to :)
User avatar
gilesgoat
Sheepie Whore
Sheepie Whore
Posts: 27259
Joined: Wed Sep 03, 2003 7:54 am 0
Location: Monfalcone, Italy
Contact:

Re: SQL question, LIKE or '=' ?

Post by gilesgoat »

And this is exactly why I asked ..

"in my logic" I would think that "like" is a "like" and "=" should be more like an "==".

I have/had no idea is that kind of "=" behaviour of MySQL is 'standard' or as you say 'is like that because the guy decided so'.

I think Gaz's solution is probably the more appropiate.

[edit] - checking the manual, I think LCASE(TRIM()) woudl do even better.
Last edited by gilesgoat on Tue Mar 06, 2012 5:45 pm 0, edited 1 time in total.
Giles the billy goat
"Baa"
User avatar
Colonius
Baaaa!
Baaaa!
Posts: 787
Joined: Tue Dec 13, 2005 8:41 pm 0
Location: Wiltshire

SQL question, LIKE or '=' ?

Post by Colonius »

On SQL 2000 the optimiser is sometimes reluctant to use an index with LIKE even when the reduced IO would make it worthwhile, we've had to use index hints at times to encourage it.
I made my song a coat
Covered with embroideries
User avatar
disciple
Sheepie Whore
Sheepie Whore
Posts: 13941
Joined: Mon Oct 27, 2003 11:33 pm 0
XBL Gamertag: LiberMentis
PSN: Prime_Heretic
Location: Greenfield
Contact:

Re: SQL question, LIKE or '=' ?

Post by disciple »

gilesgoat wrote:And this is exactly why I asked ..

"in my logic" I would think that "like" is a "like" and "=" should be more like an "==".

I have/had no idea is that kind of "=" behaviour of MySQL is 'standard' or as you say 'is like that because the guy decided so'.

I think Gaz's solution is probably the more appropiate.

[edit] - checking the manual, I think LCASE(TRIM()) woudl do even better.
Bit confused, why do you need to force lowercase if the first two examples you posted work?

That trim lcase is making the DB server do extra work on every single row for no obvious reason (like making your server wear heavy shoes). And it's pretty likely it's also going to cause your db to throw any indexes you have on that column right out of the window. (like making it wear heavy shoes and then smashing its kneecaps in with a big hammer).
PS4 : Prime_Heretic
Steam: bitstorm
Elite Dangerous: Cmdr Britain
User avatar
pgh
Posts: 18
Joined: Sat Feb 05, 2011 6:29 pm 0
Location: Cambridge, MA, USA

Re: SQL question, LIKE or '=' ?

Post by pgh »

FWIW, the ANSI SQL standard says something like this:

LIKE is a pattern condition used for matching with the wildcards % (multi char) and '_' (single char).
e.g. 'b%na' would match 'banana', and 'b_nana' would also match 'banana'.

'=' is used to match on exact equivalence
e.g. 'banana' = 'banana' (and nothing else)

The reason for the distinction, is so that exact match (the more common case) can have a shorter code path. In the '=' case the query engine can completely avoid invoking all the pattern matching stuff. Also, if there's an index then it will be used (fast), whereas with a pattern match each row is examined in turn (slow).

Similarly using explicit functions on a column (...where LCASE(RTRIM(name) = ...) is going to mean slower execution than matching on equivalence as each row is trimmed and case-converted, then checked for a match.

If all of the data in the column conforms to a standard (e.g. all upper case or lower case) then you can programatically supply the correct case, or if you can't do that apply the function on the other side of the filter ( ... where name = LCASE(RTRIM('BANANA')) ...) which will only mean one case-conversion per query execution.

If your data doesn't all conform, then you can either go the slow way or introduce a new surrogate column in which everything is upper or lower case and use the above method on that. It depends on how often your query is executed for you to work out if this is computationally worth your while. (Oracle also has function-based indexes, which do something similar without creating a column.)
User avatar
defsdoor
Baaaaaa!
Baaaaaa!
Posts: 3909
Joined: Fri Jan 30, 2004 12:26 am 0
Location: Sunny Beduff

Re: SQL question, LIKE or '=' ?

Post by defsdoor »

Isn't mysql case insensitive by default ?
User avatar
disciple
Sheepie Whore
Sheepie Whore
Posts: 13941
Joined: Mon Oct 27, 2003 11:33 pm 0
XBL Gamertag: LiberMentis
PSN: Prime_Heretic
Location: Greenfield
Contact:

Re: SQL question, LIKE or '=' ?

Post by disciple »

defsdoor wrote:Isn't mysql case insensitive by default ?
Regardless, the examples Giles' gave above indicate the column in question is.

Which is why I am wondering above....
PS4 : Prime_Heretic
Steam: bitstorm
Elite Dangerous: Cmdr Britain
User avatar
Colonius
Baaaa!
Baaaa!
Posts: 787
Joined: Tue Dec 13, 2005 8:41 pm 0
Location: Wiltshire

Re: SQL question, LIKE or '=' ?

Post by Colonius »

Again MS SQL, but the index can be used in both seek and scan. Although a like clause or case-buggery (depending on the DB collation settings) means a seek to the index is not possible, scanning an index which only has the key field on may still be quicker than a complete table scan depending on how many rows match the criteria. We hit this with our third party CRM system which has hundreds of thousands of big fat rows so a scan of a narrow index followed by seeks on fat rows is still a gain over flogging through all those fat rows.
I made my song a coat
Covered with embroideries
User avatar
gilesgoat
Sheepie Whore
Sheepie Whore
Posts: 27259
Joined: Wed Sep 03, 2003 7:54 am 0
Location: Monfalcone, Italy
Contact:

Re: SQL question, LIKE or '=' ?

Post by gilesgoat »

I found out that like it or not it's all a matter of ""



/me takes coat
Giles the billy goat
"Baa"
User avatar
venusian
Baaaaaaa!
Baaaaaaa!
Posts: 6655
Joined: Wed Nov 06, 2002 2:22 pm 0
Location: South Gloucestershire

Re: SQL question, LIKE or '=' ?

Post by venusian »

Giles, are you doing the programming equivilent of the trials of Odysseus? C++,ObjC,SQL... UML next?
User avatar
gilesgoat
Sheepie Whore
Sheepie Whore
Posts: 27259
Joined: Wed Sep 03, 2003 7:54 am 0
Location: Monfalcone, Italy
Contact:

Re: SQL question, LIKE or '=' ?

Post by gilesgoat »

venusian wrote:Giles, are you doing the programming equivilent of the trials of Odysseus? C++,ObjC,SQL... UML next?
UML no .. hopefully never .. but XML I have it coming out my nose ..
Giles the billy goat
"Baa"
Post Reply