Toad World Blog

SQL Pattern Matching in Toad Data Point

Apr 26, 2022 8:00:00 AM by Dan Hotka

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. 

Blog-TW-SQLPatternMatching-IM-JY-71827

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 

 

MediaBanner-ToadWorld-600x100-IM-JY-63709.psd-1-3-1-2

 

EMP Data

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

2 LIKE starts with S

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.

3 Pattern matching where LIKE ends with 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.

4 SQL Pattern Matching - LIKE data starts with A and ends with S

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

sql pattern matching - LIKE data that contains S

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.'

LIKE contains an LL pattern

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.

SQL pattern matching - LIKE data contains an L in the second position

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.

LIKE data does NOT contain an L in the second position

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%';

LIKE data looking for lower case data

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.

Query Builder with LIKE

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.

2nd query builder with LIKE

Here is the LIKE command in the diagram.

 

Try Toad Data Point for free

Learn how Toad Data Point can help you access and prepare data faster. Seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data source with near zero transition times.

Get started with our free 30-day trial.

Already in a trial? If Toad Data Point is helping you connect, query and prepare data for faster business insights, buy it now or contact a sales representative.

 

SQL Pattern matching - query builder like command in SQL tab

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.

DanHotka_LearningSeries_Part4

 

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

Tags: Toad Data Point Toad Data Point Videos

Dan Hotka

Written by Dan Hotka

Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.

Dan's most recent book is Toad for Oracle Unleashed