SQL: Like and Not Like

[chili5]

SQL Like and Not Like

Using Regular Expressions allows you to select data that matches a certain format (or perhaps data that does not match a format). For this we use the LIKE and NOT LIKE clauses.

The first thing we are going to do is create a people table that we can use to play around with.

CREATE TABLE IF NOT EXISTS `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `people` (`id`, `name`, `date`) VALUES
(1, 'James', '2009-09-16'),
(2, 'Joe', '2009-09-30'),
(3, 'Jack', '2009-09-18'),
(4, 'Bob', '2009-09-17'),
(5, 'Tiik', '2004-09-16');

The table has three fields, id, name, and date. We can use regular expressions to find all customers who have not registered in 2009 for example. We can also use it fo find all names that start with "J" or end with "k".

I use these functions a lot. They are very useful for implementing searches in programs.

LIKE clause

Let us first select all the names of people who start with J.

We use % to indicate to match any string of any length.

So:

'J%' means to match any string that starts with "J" and followed by a string of any length.

Try this:

SELECT name FROM people WHERE name LIKE 'J%'

Return values:

James
Joe
Jack

Now using the same % wildcard, we are going to display all names that end with 'k'.

Instead of putting the % wildcard at the end of the pattern, we are going to put it at the beginning. This means to find all strings that string art with any characters (of any length) and end with k.

Example:

SELECT name FROM people WHERE name LIKE '%k'

Output:

Jack
Tiik

This code lets us find all values that end with characters.


NOT LIKE

We use NOT LIKE to find things that do not match a certain pattern. We are going to find all strings that do not start with a j.

Try out this code:

SELECT name FROM people WHERE name NOT LIKE 'j%'

Output:

Bob
Tiik

These two "functions" are really helpful for implementing searches. I use them a lot in my program when I use searches of database.

Other wildcard available are:

_ to match any character. This can be useful for locating something when you know part of it but you don't know all of it.

Say you want to find a phone number but you only know the first 2 digits, you can write a query like this:

SELECT * FROM contacts WHERE phone_number LIKE '51_-___-____'.

This will return things like:

515-111-1111
512-134-abcd

Anything that is in the format of a phone number.

This is very simple, but is VERY useful. Next, we will look at using REGEXP for even more control.

[WingedPanther]

I use LIKE and NOT LIKE all the time. +rep

[chili5]

Same here, they are really useful for implementing searches.

    /**
     *
     * @param sQuery    The SQL query that we are building
     * @param sField    The field in the database that we searching by
     * @param sCriteria The data that we want to compare against.
     * @return  A database query that can be used to perform a search
     */
    private String buildSearchQuery(String sQuery, String sField, String sCriteria) {
        if (!sQuery.contains("WHERE")) {
            // the user has not supplied data to search for
            sQuery += " WHERE ";
        } else {
            // the user has provided data to search by. so we need to add a second criteria for a search
            sQuery += " AND ";
        }
        sQuery += sField + " LIKE '" + sCriteria + "%'";
        return sQuery;
    }

I needed a program where the user could enter parts of certain fields and search based on several fields. Like they could search based on partial first name, last name, part of a cell phone etc. Like make this REALLY easy.

It probably has other uses but this is one practical application of LIKE.

[Jordan]

Very useful! +rep