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

List all data from a table

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

Search data using Where clause

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'

Search data using Where clause with And logical operator

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'

Search data using Where clause with between operator

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

Search data using Where clause with In oprerator

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

Search data using Where clause with Or operator

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

Search data using Where clause with Like operator

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

Search data using Where clause with Join clause

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:

Text search command from ApexSQL main menu

The same option is available trough ApexSQL Search toolbar:

Text search command from 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:

Text search command from context menu

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

Text search pane

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:

Choose the SQL Server and database to search data

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:

search object list

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

Additional search options

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:

search results list

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:

Filter search SQL Server data

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

See the SQL Server data details

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

Export search SQL Server data

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

 

October 22, 2020