SQL Server Find String in String

In this SQL Server tutorial, you will learn about SQL Server find string in a string.

While analysing the data, I needed to find the specific existence of the strings within the data containing strings. Actually, I needed to find the specific pattern of strings, so for that, I used the CHARINDEX() function.

Apart from the CHARINDEX() function, I have also explained more methods called PATINDEX() to find the string in the string, which is similar to the char index but provides more control over finding the string.

Let’s start,

Table of Contents

SQL Server Find String in String

To find the string within a string, SQL Server has several functions such as CHARINDEX(), PATINDEX() and SUBSTRING().

Here, find string in string means find the substring in string, so you will learn how to find the specified substring in the given string. But you can also find a complete string in a string.

I will explain each of the functions to show you how to find a string in a string.

SQL Server Find String in String Using CHARINDEX

The CHARINDEX() function in SQL Server searches the specific part of a string within another string and returns the starting position of that specific string. Otherwise, it returns 0 if that specified string is not found.

The syntax is given on how to use the CHARINDEX() function.

CHARINDEX(substringToFind, stringToSearch, startingLocationForSearching)

Where,

CHARINDEX(): A whole function returns the substring’s starting position.
substringToFind: It is the substring or character expression whose starting position you want to know.
stringToSearch: This is the source string that contains the substring that you want to find.
startingLocationForSearching: The starting position where the search will start for the substring. If you forget to specify this option, the search begins by default from the beginning of the string.

If you want to know more about CHARINDEX() function, visit this tutorial CHARINDEX Function in SQL Server.

Let’s take an example and see.

First, create a table named Customers using the query below.

CREATE TABLE Customers (
    CustomerID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Address NVARCHAR(255)
);

Insert the following records.

INSERT INTO Customers (CustomerID, FirstName, LastName, Address)
VALUES
(373275, 'Gabriel', 'Judy', '123 Main Boulevard'),
(377385, 'Joe', 'Isabella', '456 Oak St'),
(370795, 'Logan', 'Julia', '789 Pine Boulevard'),
(376977, 'Alan', 'Grace', '101 Maple Lane'),
(366868, 'Juan', 'Albert', '234 Elm Street'),
(376489, 'Willie', 'Elijah', '567 Birch Boulevard'),
(353230, 'Wayne', 'Randy', '890 Cedar Blvd'),
(351173, 'Amber', 'Denise', '321 Spruce Avenue'),
(348721, 'Danielle', 'Marilyn', '654 Ash Boulevard'),
(371731, 'Beverly', 'Gabriel', '987 Walnut Street');

Use the query below to view the Customers Table.

SELECT * FROM Customers;

Create Customers Table Before SQL Server Find String in String Using CHARINDEX

Look at the above output, the table contains three columns FirstName, LastName and Customers.

Now, suppose you need to find customers whose addresses contain the word ‘Boulevard’; for that, you can use the CHARINDEX() method, as shown below.

SELECT FirstName, LastName, Address
FROM Customers
WHERE CHARINDEX('Boulevard', Address) > 0;

SQL Server Find String in String Using CHARINDEX

From the output, you can see there are 4 customers whose address contains the word ‘Boulevard’.

Here in the query part, CHARINDEX(‘Boulevard’, Address) > 0; takes the word ‘Boulevard’ that you want to find in the address (which is the string), and second, it takes that address (in which the specified string you want to find).

Then it compares the return result from 0. As you know, CHARINDEX returns the starting position of the substring in the string if it is found; otherwise, it returns 0, so check if the returned result is > 0.

From the output, you can see 4 customers whose addresses contain the word ‘Boulevard’ because the CHARINDEX() function returns non-zero for this word, which is greater than 0, and the given word exists.

So as a result, you get 4 customers with the address containing the word ‘Boulevard’

This is how to use the CHARINDEX() function to find the string in a string.

SQL Server Find String in String Using PATINDEX

The PATINDEX() function in SQL Server acts similarly to the CHARINDEX() function you learned in the above section, but it allows you to include pattern matching using wildcards.

So you can specify the string pattern that you want to find in the other string.

The syntax is given how to use the PATINDEX() method.

PATINDEX ( '%pattern%' , string_value )

Where,

PATINDEX(): This is the function that returns the starting position of the pattern in the string.
%pattern%: It is the character string or substring that you want to find in the string, and it should surrounded by %. Also, with the pattern, you can specify the wildcards as LIKE, such as:
- Underscore (_) to match a single character.
- Brackets [] to match any characters inside it.
- [^] means do not match any character inside it.
- % to match a string of any length.
string_value: This is the source string where the starting position of the substring is found.

If you want to learn more about the PATINDEX() function, visit this tutorial PATINDEX Function in SQL Server.

Let’s take an example and understand how to use the PATINDEX() method to find a string in a string.

Take the same Customer table that you have created in the above section. And suppose you need to find the customers whose address contains the string or word ‘street’. For that, you can use the PATINDEX() here as shown below.

SELECT FirstName, LastName, Address
FROM Customers
WHERE PATINDEX('%Street', Address) > 0;

From the output, you can see that only two customers’ addresses contain the string ‘Street’.

Here, the query part PATINDEX(‘%Street’, Address) > 0; first takes the pattern ‘%Street’ that you want to find in the address, and second is the address (or string) where you want to find the given pattern (or substring).

This is how to use the PATINDEX() function in SQL Server to find the string in the string.

Conclusion

In this SQl Server tutorial, you learned about SQL Server find string in a string using the CHARINDEX() and PATINDEX() functions.

Additionally, you learnt about the syntax of both functions.