mysql query

johnN · 04-03-2008, 11:27 PM

Sorry for asking what should be a fairly simple question.

Say I have the search term "myzoo" and a database entry "zoo" how would I go about getting it to match?

Or in other terms, how can I get mysql to match the last part of a search term.

thanks very much guys, I'm incredibly stuck with this.

Sand_Devil · 04-04-2008, 02:06 AM

% is a wild card.

where blah_field like '%zoo'

Of course, if case is a problem, you can use

where upper(blah_field) like '%ZOO'

Wildhoney · 04-04-2008, 02:47 AM

MySQL is case-insensitive. Is there actually any way to tell it to be case-sensitive? Not that I need it, I'm just curious. Sand Devil is spot on though.

Salathe · 04-04-2008, 12:15 PM

Looking at Sand_Devil's example of where upper(blah_field) like '%ZOO', by default (ie, case-insensitive) this wouldn't behave any differently (except, more slowly) to where blah_field like '%zoo'.

You can tell columns or whole tables to be case-sensitive by assigning an appropriate collation (ending in _cs [case-sensitive] or _bin [binary]). If you don't want to alter your table structure, you can change the collation, or declare the value as binary, within any given query.

Code:

Table: posts

 id  title
-----------
 1   Moo
 2   moo
 3   mop

Example Queries:

SELECT title FROM posts WHERE title LIKE 'mo%';
> 3 rows returned  (Moo, moo, mop)

SELECT title FROM posts WHERE title LIKE BINARY 'mo%';
> 2 rows returned (moo, mop)

The MySQL Manual has a page on case sensitivity in searches for a little more information.

johnN · 04-04-2008, 12:39 PM

Sorry, I didn't explain properly.

I want the opposite of that.

I need this string "myzoos" to match the database entry "zoos", not the other way round as I must have suggested.

I'm using ci so upper is irrelevant.

Imagine I need to use a wildcard on the actual database entries:)

Thanks:)

SELECT FROM dictionary where word LIKE '%myzoo'

will throw up things like mamyzoo, dobdomyzoo ect,. but not just "zoo"

Sand_Devil · 04-05-2008, 04:25 AM

If I understand what you are suggesting... word being the entered string and blah_field being the database entry. || allows you to concatenate strings. To search for the % you need to place an escape, usually \

where word like ('%'||blah_field)

Should do the trick.

johnN · 04-05-2008, 08:12 PM

Quote:

Originally Posted by Sand_Devil

If I understand what you are suggesting... word being the entered string and blah_field being the database entry. || allows you to concatenate strings. To search for the % you need to place an escape, usually \

where word like ('%'||blah_field)

Should do the trick.

You are instantly and permanently my favorite person.

I had to use CONCAT but the details you gave me helped me solve it within minutes!

Thank!