Wednesday, January 30, 2008

SQL Basics - Using Wildcards With The LIKE Operator

If you've worked with computers you know about wildcards. In Windows '*' stands for an indefinite number of characters and '?' means one character. You can do a similar representation in SQL WHERE statements. In most SQL applications the '*' is replaced with '%' and the '?' is replaced by the '_' (underscore) symbol. In Microsoft Access you must use the standard wildcards, * and ?. The way we use it is with the LIKE operator:

SELECT * FROM column_name WHERE LIKE '__sample*'

What the above example does is find entries in column_name that have any two characters, then the word 'sample', then whatever else.

Here are some examples of the syntax:

SELECT child_name FROM TeethEyes

WHERE child_name LIKE '__rr%';


In Access you would type: WHERE LIKE '??rr*'

Here's another example:

SELECT child_name FROM TeethEyes

WHERE child_name LIKE '%y';

Like 2

IMPORTANT NOTE: I think I failed to mention all along the difference when entering text strings or entering numbers. When entering text in the WHERE clause, the text is surrounded by 's. such as 'this'. Numbers are entered without the apostrophes. I'll edit the first WHERE post to note this there too.

No comments: