MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 7.2: WHERE ... LIKE

2009-04-16 21:15:47 | 145 views | sql mysql table where like

WHERE ... LIKE



Before explaining how LIKE works it's good to know how the wildcharacter works in the SQL. The wildcharacter is a percentagesign, '%', and works sort of like you expect the '*', asterix, sign to work. If you search for '%ty' you're searching for everything that ends with 'ty', like Party, Marty or Patty. Searching for '%oo%' searches for everything that starts with something(anything), continues with 'oo' and ends with anything, like Pool, Cool or Powertool.

Now, the wildcharacter is used in conjunction with the LIKE keyword.


This will be our table 'person'

|--------------------+--------------------+--------------------|
|Name |Age |Gender |
|____________________+____________________+____________________|
|Bob 34 Male |
|Silvia 67 Female |
|Magnus 24 Male |
|Lisa 32 Female |
+--------------------------------------------------------------+



Syntax:

SELECT column
FROM table_name
WHERE column LIKE condition


Example:


SELECT *
FROM person
WHERE Name LIKE '%i%'


|--------------------+--------------------+--------------------|
|Name |Age |Gender |
|____________________+____________________+____________________|
|Silvia 67 Female |
|Lisa 32 Female |
+--------------------------------------------------------------+


Example 2:


SELECT *
FROM person
WHERE Age LIKE '3%'


|--------------------+--------------------+--------------------|
|Name |Age |Gender |
|____________________+____________________+____________________|
|Bob 34 Male |
|Lisa 32 Female |
+--------------------------------------------------------------+


As we can see, even with Integers we need to write the condition within '´s.

You can of course, also use NOT LIKE if you want to:

Example 2:


SELECT *
FROM person
WHERE Age NOT LIKE '3%'


|--------------------+--------------------+--------------------|
|Name |Age |Gender |
|____________________+____________________+____________________|
|Silvia 67 Female |
|Magnus 24 Male |
+--------------------------------------------------------------+





Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.