Different methods to search SQL Server data

One of the most basic and repetitive operations in the SQL world is to search SQL Server data. Usually used to locate specific row items, sometimes for exporting purposes, or check if dependent values are properly populated.

In this article, a couple of ways to search SQL Server data will be shown. In the first part, a common way to search for data in a database will be explained by using the Where clause and operators in the Select statements. At the end of the article, the Text search feature of the ApexSQL Search add-in will be shown and its solution to search SQL Server data in a database.

Where text search

The SQL Server Where clause is used to filter rows (results) returned by a query from a Select, Insert, Update, or Delete statement.

When the Select statement is used, entire rows from the table will be returned, which, in many cases, is unnecessary. To get results from a table that will satisfy your needs, one or more search conditions under the Where clause must be specified which will filter rows returned by the From clause.

Base where condition:

SELECT
    List desired table columns
FROM
    Table name
WHERE
    Search condition;

In the code below two examples are shown, the first one is without the Where clause in the Select statement and another one is with the Where clause and one condition:

First example:

 SELECT p.BusinessEntityID, 
        p.PersonType, 
        p.NameStyle, 
        p.Title, 
        p.FirstName, 
        p.MiddleName, 
        p.LastName, 
        p.Suffix, 
        p.EmailPromotion, 
        p.AdditionalContactInfo, 
        p.Demographics, 
        p.rowguid, 
        p.ModifiedDate
FROM Person.Person p

As it can be seen, about 20 000 rows are returned, which is not much of use if we want to see only data for the persons whose type is e.g. EM.

To search SQL Server data EM value, that is located under the PersonType column of the Person table, in the Where clause set the condition.

Second example:

 SELECT p.BusinessEntityID, 
        p.PersonType, 
        p.NameStyle, 
        p.Title, 
        p.FirstName, 
        p.MiddleName, 
        p.LastName, 
        p.Suffix, 
        p.EmailPromotion, 
        p.AdditionalContactInfo, 
        p.Demographics, 
        p.rowguid, 
        p.ModifiedDate
FROM Person.Person p
WHERE p.PersonType = 'EM';

To narrow down the search scope, under the Where clause, the Comparison or Logical operators can be included.

To find data that meet two conditions, under the Where clause the logical operator And needs to be used.

In the example below, a new condition is added using the And logical operator:

 SELECT p.BusinessEntityID, 
        p.PersonType, 
        p.NameStyle, 
        p.Title, 
        p.FirstName, 
        p.MiddleName, 
        p.LastName, 
        p.Suffix, 
        p.EmailPromotion, 
        p.AdditionalContactInfo, 
        p.Demographics, 
        p.rowguid, 
        p.ModifiedDate
 FROM Person.Person p
 WHERE p.PersonType = 'EM' AND p.LastName = 'Li'

To find the rows with the value between two values, then, with the Where clause, the Between operator can be used

The example below finds the modified date between ‘2007-12-30’ AND ‘2009-01-07’:

 SELECT p.BusinessEntityID, 
        p.PersonType, 
        p.NameStyle, 
        p.Title, 
        p.FirstName, 
        p.MiddleName, 
        p.LastName, 
        p.Suffix, 
        p.EmailPromotion, 
        p.AdditionalContactInfo, 
        p.Demographics, 
        p.rowguid, 
        p.ModifiedDate
 FROM Person.Person p
 WHERE p.ModifiedDate BETWEEN '2007-12-30' AND '2009-01-07'

To specify multiple values in a Where clause, the Inoperator can be used. Basically, the IN operator replaces multiple usage of Or conditions in a Select, Insert, Update, or Delete statement:

 SELECT p.BusinessEntityID, 
        p.PersonType, 
        p.NameStyle, 
        p.Title, 
        p.FirstName, 
        p.MiddleName, 
        p.LastName, 
        p.Suffix, 
        p.EmailPromotion, 
        p.AdditionalContactInfo, 
        p.Demographics, 
        p.rowguid, 
        p.ModifiedDate
 FROM Person.Person p
 WHERE p.PersonType IN ('EM','VC', 'SC')

The same result will be accomplished when using the multiple Or operator:

 SELECT p.BusinessEntityID, 
        p.PersonType, 
        p.NameStyle, 
        p.Title, 
        p.FirstName, 
        p.MiddleName, 
        p.LastName, 
        p.Suffix, 
        p.EmailPromotion, 
        p.AdditionalContactInfo, 
        p.Demographics, 
        p.rowguid, 
        p.ModifiedDate
 FROM Person.Person p 
 WHERE p.PersonType = 'EM' OR p.PersonType ='VC' OR p.PersonType = 'SC';

An often used operator in conjunction with the Where clause is the Like operator. With this operator, it can be determined whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. The Like operator is commonly used with a combination of the % (percent) and _ (underscore ) wildcard character. The percent replaces zero, one, or multiple characters while underscore replaces a single character:

 SELECT p.BusinessEntityID, 
        p.PersonType, 
        p.NameStyle, 
        p.Title, 
        p.FirstName, 
        p.MiddleName, 
        p.LastName, 
        p.Suffix, 
        p.EmailPromotion, 
        p.AdditionalContactInfo, 
        p.Demographics, 
        p.rowguid, 
        p.ModifiedDate
 FROM Person.Person p 
 WHERE p.PersonType LIKE '%EM%';

To search SQL Server data over multiple tables, the Join clause can be used. The Join clause combines rows from two or more tables, based on a related column between them:

 SELECT p.BusinessEntityID, 
        p.PersonType, 
        p.FirstName, 
        p.MiddleName, 
        p.LastName, 
        p.Suffix,
        e.JobTitle, 
        e.BirthDate
 FROM Person.Person p
 INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
 WHERE p.PersonType LIKE '%EM%';

The issue with these T-SQL methods is that the more refined search result is required the complicated query becomes. A method to search for specific data, without typing any of SQL code, with high data granularity and filtering options, would be quite beneficial.

Search SQL Server data using a third-party tool

ApexSQL Search is a SQL Server Management Studio (SSMS) and Visual Studio add-in used to search SQL Server data. It can search the text for data stored in tables and views; also the text within database objects (including object names) can be searched.

To start searching data in SSMS or Visual Studio main menu, click the ApexSQL Search and, from the list, choose the Text search command:

The same option is available trough ApexSQL Search toolbar:

Or from the Object Explorer pane, select a database to search data from, right-click and, from the context menu, choose the Text search command:

Once the command is clicked, the Text search pane will appear:

In the Search text box, enter a search phrase to find data that maches it. From the Server drop-down box, choose the server that hosts the database against which the search data needs to be performed, and from the Database drop-down list, choose the database:

Under the Select objects to search, list chooses the tables or views to search in. In our example, all objects in the list will be selected:

Additionally, specify types of columns the search should be performed against, by selecting corresponding check-boxes: numeric, text type, unique identifier, or date columns:

Once all is set, click the Find button. In the Text search, result grid will be populated with objects that contain the search string specified in the Search text box:

As illustrated, the search string in the chosen database appears about 18000 times, which is a lot. Let’s narrow down search results by filtering data from the search results list. Click on the filter icon which is located under the headers of the search results columns and, from there, choose the additional filter option:

By clicking on the ellipsis button next to the Columns values column, the object details can be seen:

All results from the search results list can be saved/exported in one of the available formats like XML, CSV, HTML:

Conclusion

Using a reliable and easy method to search SQL Server data can be a way to reduce required time and stress from these types of tasks thus increasing overall productivity on a daily basis