[thelist] mysql pattern matching query

Bob Meetin bobm at dottedi.biz
Thu Jul 16 23:22:05 CDT 2009


Example fields in the database:

username
name

Some folks register for accounts using a username that is like or 
actually equal to their first or last name.  I tried something like:

  mysql> select id, name, username from users where name like '%username%';

which of course returns NULL because it is trying to match the word, 
username, rather than the username field.

Q1: How do you tell mysql to match the field value?

You will occasionally see folks who register with an identical first and 
last name like "Mary Mary"

  mysql> select id, name, username from users where name like '%username 
username%';

Q2: Similar to Q1 but will find members who lazily use the same 
username, first name and last name?

    mysql> select id, name, username from users where lower(name) like 
lower('%username%');

Q3: Will something like the above work to convert and match in lower case?

-Bob




More information about the thelist mailing list