Applies to
Summary
This article describes how to search through the results grid to find text, wildcards, and regular expressions. Although this might sound straightforward, there is no native SSMS functionality to search through the Results grid. This is where ApexSQL Complete‘s Results search feature come very handy.
Description
ApexSQL Complete is add-in for SSMS and Visual Studio that speeds up coding and improve productivity using features such as SQL snippets, auto-replacements, auto-inserting statements and more.
One of the many features is the Results search that allows to search for matching strings, wild cards, and regular expressions in current grid, selection or across all result grids.
The Results search feature is accessible via ApexSQL Complete main menu, by clicking the Results search option, or by activating it with the shortcut Ctrl+Q, Ctrl+R on the keyboard:
Another way to access the feature is via the right-click menu in the Results grid by clicking the Results search command in the context menu:
Make sure that a query is executed, before accessing the Results search feature. Otherwise, a pop-up message is displayed informing to execute a query first, like the one bellow:
Text search
Let’s start with a simple example. The AdventureWorks2014 database is used for every example in this article.
A set of results is returned from a simple Select query:
SELECT a.AddressLine1 ,a.City FROM Person.Address a
The query will return 19,614 rows. In order to search in column City for a substring, another query could be used:
SELECT a.AddressLine1 ,a.City FROM Person.Address a WHERE a.City LIKE 'Paris'
The second query will return 398 rows. But, in this case, all of the rows needs to be returned and searched for various results, including Paris. In such cases, the ability to perform a post-execution grid results search is very handy.
This is accomplished with the Results search feature. In the Search term box type Paris, leave the Matching type option to Text, and click the Highlight all button. Cells that contain found value are highlighted:
Note that the total number of found results is bigger than 398, which is the number we got using the query as a search option. This is because some of the cells contains more than just the string Paris. By checking the Match whole cell content option under the Case options group, the total number is the same as before:
Searching through the results grid using wild cards
Next, let’s see how the Wild cards option under the Matching type option group works. Type a simple Select query, followed by a Where clause:
SELECT * FROM Person.Address a WHERE a.City LIKE 'Los%';
A wildcard character, %, is used to substitute for any other character(s) in a string. The example above finds any values that starts with “Los”. The query returns 93 rows, but like as before, rather do this as the post-execution. So, execute the same query, only this time without the Where clause:
SELECT * FROM Person.Address a
This time, 19,614 rows are returned. Go to the Results search window again, type Los* in the Search term box, and check the Wild cards option for the Matching type option. The search will find any values that starts with the word Los, and highlight results in the Results grid. Note that the total found number is 93, which is the same as after executing the first query:
Searching through the results grid using regular expressions
Lastly, the Results search feature allows for the use of regular expressions. Regular expressions are considered a more advanced tool to find and manipulate text. However, regular expressions take a bit of getting used to.
This is a simple example. Execute the same query from previous case:
SELECT * FROM Person.Address a WHERE a.City LIKE 'Los%';
In the Search term box type “[A-Z]”, check the Regular expressions option under the Matching type option group, and click the Highlight all button. In this example, the search term indicates that any character from A to Z (inclusive and case-sensitive) will cause a match:
Up until now, each search is performed for one Results grid, but ApexSQL Complete also allows searching through multiple Results grids. To demonstrate, let’s execute a query with two Select statements, to get two grids with data to search for:
SELECT * FROM Person.Address a SELECT * FROM HumanResources.Employee e
From the Look in drop-down menu, choose the All results grid(s) option to search across multiple grids:
Type “128” in the Search term box, and click the Highlight all button. In both Results grids, fields will be highlighted that match search criteria entered in the Search term box:
When performing a search, make sure to select where to look for results. From the Look in drop-down menu, there are three options.
So far, in this article there was an example with the Current results grid and All results grid(s). Now, let’s demonstrate the last Current results grid selection option.
Run a Select statement that returns e.g. more than 20,000 rows, it’s not necessarily needed to search across the whole Results grid. The Results search feature also provide search through only data that are selected in the Results grid.
Let’s run a query and return a bigger number of rows:
SELECT * FROM Sales.SalesOrderDetail sod
In the Sales.SalesOrderDetail table, there are 121,317 rows. Let’s say that only first 20 rows need to be searched, where order quantity (OrderQty) is “1”. Simply make a selection in the Results grid, and in the Search term box, enter “1”. From the Look in drop-down box, choose the Current results grid selection option and click the Highlight all button. Also, make sure to check the Match whole cell content option, as the SalesOrderID column is also selected in which “1” is a part of some IDs, and the user doesn’t want those returned. In the Results grid, cells are highlighted that match with the string in the Search term box, and the total found number which is 10:
|
Quick tip: When performing multiple searches across the same Results grid, make sure to check the Clear previous grid selection option, so that a fresh selection after every time search terms are changed |
