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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


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