Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 13, 1998

Introduction to Databases for the Web: Pt. 2 - Page 4

By Selena Sol

Like

The LIKE operator is another extremely powerful tool to help you define your search. In its most simple form, the LIKE operator functions just like the "=" operator. That is, it matches for equality in the WHERE clause. Thus, if we were to search our EMPLOYEES table, the statement:

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE 
   EMP_NAME = 'Lim Sing Yuen';

Would be the same as saying:

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE 
   EMP_NAME LIKE 'Lim Sing Yuen';

In either case, you would get:

EMP_NAME      EMP_NUM
---------------------
Lim Sing Yuen     002
---------------------

However, the real umph of the LIKE operator comes from its ability to incorporate wildcards. Specifically, the LIKE operator can be used with the "%" and the "_" operator. The "%" operator can be used to match any string of any length and the "_" operator is used to match any single character. But this is best seen by example. Consider the following:

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE EMP_NAME LIKE 'Lim%';

In this case, you would get the following:

EMP_NAME       EMP_NUM
----------------------
Lim Li Chuen       001
Lim Sing Yuen      002
----------------------

Notice that in both cases, the search string begins with "Lim". The "%" wildcard specifies that so long as the string begins with the string "Lim", it matches.

Consider the "_" operator.

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE EMP_NUM LIKE '00_';

In this case, you would get all the records returned that included a "00" followed by any other character:

EMP_NAME       EMP_NUM
----------------------
Lim Li Chuen       001
Lim Sing Yuen      002
Loo Soon Keat      003
----------------------

Null

A final WHERE clause operator is the IS NULL/IS NOT NULL operator. This operator is used to test whether or not a column has a value or not. For example, we can grab all the columns from the EMPLOYEES table that have employee numbers using the following command.

SELECT EMP_NAME, EMP_NUM 
FROM EMPLOYEES
WHERE EMP_NUM IS NOT NULL;

In response, you would get:

EMP_NAME       EMP_NUM
----------------------
Lim Li Chuen       001
Lim Sing Yuen      002
Loo Soon Keat      003
----------------------


SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date