Understanding SQL Server’s PATINDEX Function

Introduction

When working with strings in SQL Server, it’s common to encounter situations where we need to find specific substrings within larger strings. One powerful function that can help us achieve this is the PATINDEX function.

The PATINDEX function is used to find the position of a specified pattern within a string. The function takes two arguments: the first is the pattern to search for, and the second is the string in which to search for the pattern.

SQL Server’s PATINDEX Function Syntax

The syntax of the PATINDEX function is as follows:

{< highlight lang="sql" >}
PATINDEX ( pattern, string )
{< /highlight >}

In this syntax:

• pattern: This is the substring or regular expression that we want to search for within the string.
• string: This is the string in which we want to find the specified pattern.

SQL Server’s PATINDEX Function Examples

Here are a few examples of using the PATINDEX function:

{< highlight lang="sql" >}
-- Find the position of "hello" within "hello world"
SELECT PATINDEX('%hello%', 'hello world');

-- Find the position of ".com" within "example.com"
SELECT PATINDEX('%\.com%','.com');

In these examples, we use the % wildcard to search for any characters before and after the specified pattern. This allows us to find the position of substrings within larger strings.

SQL Server’s PATINDEX Function with Regular Expressions

While PATINDEX does not support regular expressions in its basic syntax, we can use the REPLACE function along with the PATINDEX function to simulate this behavior.

For instance, if we want to find all occurrences of a specific pattern within a string, we can use the following code:

{< highlight lang="sql" >}
DECLARE @pattern NVARCHAR(100) = 'hello';
DECLARE @string NVARCHAR(100) = 'hello world';

WHILE PATINDEX('%' + @pattern + '%', @string) > 0
BEGIN
    PRINT (SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string), LEN(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string), LEN(@pattern)))));

    SET @string = REPLACE(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)), SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string), LEN(@pattern)), '');
END
{< /highlight >}

In this code, we use a WHILE loop to continuously find the position of the specified pattern within the string. We then print out the substring that matches the pattern and replace it with an empty string so that the next iteration can start searching from the beginning of the remaining string.

SQL Server’s PATINDEX Function with Substrings

In our previous example, we used a WHILE loop to find all occurrences of a specified pattern within a string. However, this approach is not very efficient when dealing with large strings.

A more efficient way to find all occurrences of a specified pattern within a string would be to use the following code:

{< highlight lang="sql" >}
DECLARE @pattern NVARCHAR(100) = 'hello';
DECLARE @string NVARCHAR(100) = 'hello world';

CREATE TABLE #outTable (substring NVARCHAR(100));
INSERT INTO #outTable SELECT SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string), LEN(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string), LEN(@pattern))));
{< /highlight >}

In this code, we create a temporary table #outTable to store the substrings that match the specified pattern. We then use an INSERT INTO statement to populate this table with the substrings.

This approach is more efficient because it avoids the need for a WHILE loop and allows us to process all occurrences of the specified pattern in a single operation.

SQL Server’s PATINDEX Function with Substrings (Alternative)

Alternatively, we can use the following code:

{< highlight lang="sql" >}
DECLARE @pattern NVARCHAR(100) = 'hello';
DECLARE @string NVARCHAR(100) = 'hello world';

SELECT substring 
FROM (
    SELECT LEFT(@string, PATINDEX('%' + @pattern + '%', @string)) AS leftSubstring
    UNION ALL
    SELECT SUBSTRING(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)), PATINDEX('%' + @pattern + '%', SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern))), LEN(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)))) AS rightSubstring
    FROM @string
) AS t 
WHERE leftSubstring IS NOT NULL OR RIGHT(substring, LEN(rightSubstring) - LEN(leftSubstring)) = rightSubstring;
{< /highlight >}

In this code, we use a subquery to generate the left and right substrings that match the specified pattern. We then select the substring that matches both the left and right parts.

This approach is more efficient because it avoids the need for an explicit loop and allows us to process all occurrences of the specified pattern in a single operation.

SQL Server’s PATINDEX Function with Substrings (Using Recursive CTE)

Alternatively, we can use the following code:

{< highlight lang="sql" >}
DECLARE @pattern NVARCHAR(100) = 'hello';
DECLARE @string NVARCHAR(100) = 'hello world';

WITH RecursiveCTE AS (
    SELECT LEFT(@string, PATINDEX('%' + @pattern + '%', @string)) AS leftSubstring,
           SUBSTRING(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)), PATINDEX('%' + @pattern + '%', SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern))), LEN(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)))) AS rightSubstring,
           CASE WHEN PATINDEX('%' + @pattern + '%', SUBSTRING(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)), 1, LEN(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)))) = 0 THEN NULL ELSE SUBSTRING(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)), PATINDEX('%' + @pattern + '%', SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern))), LEN(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)))) END AS endSubstring
    FROM @string
    UNION ALL
    SELECT leftSubstring,
           SUBSTRING(rightSubstring, 1, LEN(rightSubstring)) AS rightSubstring,
           CASE WHEN PATINDEX('%' + @pattern + '%', SUBSTRING(rightSubstring, 1, LEN(rightSubstring)) = 0 THEN NULL ELSE SUBSTRING(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)), PATINDEX('%' + @pattern + '%', SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern))), LEN(SUBSTRING(@string, PATINDEX('%' + @pattern + '%', @string) + LEN(@pattern)))) END AS endSubstring
    FROM RecursiveCTE
)
SELECT leftSubstring 
FROM RecursiveCTE 
WHERE endSubstring IS NOT NULL;
{< /highlight >}

In this code, we use a recursive common table expression (CTE) to generate the left and right substrings that match the specified pattern. We then select the substring that matches both the left and right parts.

This approach is more efficient because it avoids the need for an explicit loop and allows us to process all occurrences of the specified pattern in a single operation.

Conclusion

In this article, we have discussed how to use SQL Server’s PATINDEX function to find all occurrences of a specified pattern within a string. We have presented several approaches to achieve this goal, including using a WHILE loop, recursive CTEs, and other techniques.

Each approach has its own advantages and disadvantages, and the choice of which one to use depends on the specific requirements of your project. By choosing the most suitable approach, you can efficiently process large strings and find all occurrences of the specified pattern.

Example Use Case

Here is an example use case for the PATINDEX function:

-- Create a table to store the substrings that match the specified pattern
CREATE TABLE #outTable (substring NVARCHAR(100));

-- Insert data into the table
INSERT INTO #outTable SELECT 'hello world' AS string;

-- Select all occurrences of the specified pattern from the table
SELECT * FROM #outTable;

In this example, we create a table #outTable to store the substrings that match the specified pattern. We then insert data into the table using an INSERT INTO statement.

Finally, we select all occurrences of the specified pattern from the table using a SELECT statement. The result set will contain all substrings that match the specified pattern.

Notes

• The PATINDEX function returns the starting position of the first occurrence of the specified pattern within the string.
• The LEN function returns the length of the specified substring.
• The SUBSTRING function returns a portion of the original string.
• The REPLACE function replaces a specified string with another string in the original string.

Last modified on 2023-08-23