SQL allows for the selectivity of character data with a specific pattern and NOT in a specific pattern. Either way, pattern matching syntax will always be useful when sifting through your extensive data stores. 

For example, you have a million rows of data, but you just want Zachery Smith's information. You do not have any other data other than the name (or maybe even a partial name). The LIKE syntax allows you to search for an entire character sequence or any part of the sequence. For this example, the data iis all in upper case. 

Without going into great detail on data retrieval performance topics, if you can always start your pattern matching character searches with an initial character, the query will perform much better. That being said, you can search on any pattern needed, but the performance might be significantly better if you can start with, say, an 'S' for a SMITH kind of search. 

Maybe you are doing some data scrubbing and need to find two "L" in the 3rd and 4th position of a field. The LIKE pattern matching command has two kinds of global characters: one skips a letter location and the other is a global skip (skips any number of character positions). 

You might be looking for error data where “Mr.” may have been spelled out and you wish to change all of these to something else. In this instance, the NOT LIKE syntax might be helpful. 

The LIKE syntax for pattern matching is your friend. 

This blog will focus on the pattern matching SQL using Traditional Toad Data Point and will illustrate taking similar actions using Query Builder (QB). Query Builder is more for the novice and allows for queries to be built without typing. This feature also has a 'query' tab that allows for the SQL to be reviewed and tweaked.

Using the LIKE Syntax 

The LIKE command has two search characters: 

• % skips a group of characters 

• _ skips a single position 

This query shows us the ENAME data this blog will use to illustrate the LIKE command functions.

This where clause shows the ENAMEs that start with S. The % sign tells SQL to skip any number of characters after the S. This pattern matching is interpreted as 'begins with' an S.

This query shows the ENAMEs that end with S. The % sign here tells SQL to skip any number of characters till the last character. This pattern is interpreted as 'Ends With' an S.

This example shows a mix of both 'begins with' and 'ends with.'

Let's review this one. %<text string>% is a 'contains' the character string. In this example, we’re just looking for the ENAMEs that contain an 'S.'

This query tells SQL to skip any number of characters until you get to an LL pattern, then to skip the remaining characters as well. This pattern matching is interpreted as 'contains' an LL.

This query tells SQL to skip the first character, look for an L in the 2nd position, then skip the remaining characters. This pattern matching is interpreted as 'contains starting at position 2) an L.

There is also a negative LIKE pattern matching syntax (NOT LIKE) that will return everything but the pattern identified. The NOT LIKE syntax might be useful if you are doing some data scrubbing and would like all data to be capitalized in the database. You could look for names that start with A (using the upper function, covered later in this series) and have an 'and' clause that negates the capital A in the actual text.

The syntax might look like this:

WHERE upper(ENAME) LIKE 'A%'

And ENAME NOT LIKE 'A%';

The EMP data in our example here does not contain any lowercase letters.

Toad Data Point Helpful Techniques

I will now use Query Builder to show one of the LIKE commands from above. Easy enough to do and for the LIKE command, I prefer the Editor and making slight adjustments to the SQL. You can do something similar with Query Builder by adjusting the SQL in the Query Tab.

You again click on the '…' on the ENAME Where clause line in the diagram. The click on the dots will bring up this panel. Change the left drop down pick list to LIKE and you will get the pattern on the right. Notice that it handles all of the above-used LIKE examples.

Here is the LIKE command in the diagram.

Here is the LIKE command in the Query tab. Now, you can easily change this LIKE command to another desired pattern without going back through the diagram's menu system.

This SQL Learning Series covered SQL pattern matching using the LIKE syntax. When scrubbing data or looking for specific information within a database, this syntax, paired with Toad Data Point, can help analysts easily find and prepare data.

Related Links

Toad Data Point Tutorials: Data setup, code snippets and SQL Recall

Toad Data Point Tutorials: SQL Where Clause Null Values

How to use Quest® Toad® Data Point to cleanse, transform, save and retrieve data

Access and query data with a repeatable process using Quest® Toad® Data Point

How to use Quest® Toad® Data Point to Securely Save and Share Data Sets