SQL PATINDEX() String Function

The SQL PATINDEX() function returns the starting position of the first occurrence of a specified pattern in a string, or 0 if the pattern is not found. This function is especially useful for locating substrings within larger text values based on wildcard patterns.

PATINDEX() is available in SQL Server, and it allows you to use wildcard characters such as % and _ within the pattern for more flexible matching.

In this tutorial, we will go through SQL PATINDEX() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL PATINDEX() Function

The basic syntax of the SQL PATINDEX() function is:

</>
Copy
PATINDEX('%pattern%', string);

Each part of this syntax has a specific purpose:

  • pattern: The pattern you are searching for in the string, enclosed in % symbols to allow pattern matching.
  • string: The text to be searched. This can be a column, variable, or text literal.

The PATINDEX() function returns an integer indicating the starting position of the pattern in the string. If the pattern is not found, it returns 0.

Setup for Examples: Creating the Database and Table

We’ll create a sample documents table with fields title and content to demonstrate the PATINDEX() function examples.

1. First, create a new database called library:

</>
Copy
CREATE DATABASE library;

2. Select the library database to work with:

</>
Copy
USE library;

3. Create a table named documents with fields document_id, title, and content:

</>
Copy
CREATE TABLE documents (
    document_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    content TEXT
);

4. Insert sample data into the documents table to use with the PATINDEX() function examples:

</>
Copy
INSERT INTO documents (title, content)
VALUES 
('Introduction to SQL', 'This document explains the basics of SQL and database management.'),
('Data Science Applications', 'Data-driven approaches are essential in data science and analytics.'),
('Server Management Guide', 'Learn how to configure and manage servers effectively.'),
('Logging Best Practices', 'This guide covers best practices for logging and log management.');

With this setup complete, you can run the PATINDEX() function examples to test and view results in the documents table.

Examples: Using PATINDEX() in SQL Queries

We’ll go through examples demonstrating the PATINDEX() function in SQL, using it to locate specific words or patterns within text data in the documents table.

1. Finding the Position of a Word in Text

To find the position of the word “SQL” in the content column:

</>
Copy
SELECT content, PATINDEX('%SQL%', content) AS position
FROM documents;

This query returns the starting position of the first occurrence of “SQL” in each content entry. If “SQL” is not found, it returns 0.

2. Using Wildcards with PATINDEX()

To locate a word that starts with “data” (e.g., “database”, “data-driven”) in the content column:

</>
Copy
SELECT content, PATINDEX('%data%', content) AS position
FROM documents;

This query finds the first occurrence of any word starting with “data” in each content field. The % wildcard allows for flexible matching.

3. Filtering Records Based on PATINDEX() Result

To filter rows where the content contains the word “server”:

</>
Copy
SELECT content
FROM documents
WHERE PATINDEX('%server%', content) > 0;

This query returns rows where the word “server” appears in the content column. The condition PATINDEX() > 0 ensures that only rows with matches are returned.

4. Using PATINDEX() with Multiple Wildcards

To search for words containing “log” (e.g., “log”, “logging”, “catalog”) in the content column:

</>
Copy
SELECT content, PATINDEX('%log%', content) AS position
FROM documents;

This query finds words containing “log” anywhere within the string, returning the starting position or 0 if “log” is not found.

FAQs for SQL PATINDEX()

1. What does the SQL PATINDEX() function do?

The PATINDEX() function returns the starting position of the first occurrence of a pattern within a string, or 0 if the pattern is not found.

2. How does PATINDEX() handle patterns?

PATINDEX() supports wildcards (% and _) within the pattern for flexible matching. Patterns must be enclosed within % signs.

3. Can PATINDEX() handle case sensitivity?

Case sensitivity in PATINDEX() depends on the database collation settings. By default, it is case-insensitive in SQL Server unless a case-sensitive collation is applied.

4. Is PATINDEX() supported in all SQL databases?

No, PATINDEX() is specific to SQL Server. Other databases may have similar functions like INSTR() in MySQL for pattern matching.

5. What happens if the pattern is not found?

If the pattern is not found in the string, PATINDEX() returns 0.