SQL Server String PATINDEX() Function

SQL Server PATINDEX() Function

The SQL Server PATINDEX() function is used to return the starting position of a pattern in a specified expression, or zero if the pattern is not found. This function is useful for searching for a substring within a string.

Syntax

SELECT PATINDEX('%pattern%', expression);

The PATINDEX() function takes two arguments:

  • pattern: The substring to search for, enclosed in percentage signs to allow for wildcard searches.
  • expression: The string expression to search within.

Example SQL Server PATINDEX() Function Queries

Let's look at some examples of SQL Server PATINDEX() function queries:

1. Basic PATINDEX() Example

SELECT PATINDEX('%World%', 'Hello World') AS position;

This query searches for the substring 'World' in the string 'Hello World' and returns the starting position of the first occurrence. The result will be:

position
--------
7

2. PATINDEX() with a Column

SELECT first_name, PATINDEX('%a%', first_name) AS position
FROM employees;

This query searches for the substring 'a' in the first_name column for each employee and returns the starting position of the first occurrence.

3. PATINDEX() with a Variable

DECLARE @myString VARCHAR(50);
SET @myString = 'SQL Server';
SELECT PATINDEX('%Server%', @myString) AS position;

This query uses a variable to store a string and then searches for the substring 'Server' in it. The result will be:

position
--------
5

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the PATINDEX() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    description VARCHAR(50)
);

In this example, we create a table named example_table with columns for id and description.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, description) VALUES (1, 'Apple');
INSERT INTO example_table (id, description) VALUES (2, 'Banana');
INSERT INTO example_table (id, description) VALUES (3, 'Cherry');

Here, we insert data into the example_table.

Step 3: Using the PATINDEX() Function

This step involves using the PATINDEX() function to search for substrings within the description column.

SELECT id, description, PATINDEX('%a%', description) AS position
FROM example_table;

This query retrieves the id, description, and the starting position of the substring 'a' in the description column for each row in the example_table. The result will be:

id  description  position
--- ------------ --------
1   Apple        0
2   Banana       2
3   Cherry       0

Conclusion

The SQL Server PATINDEX() function is a powerful tool for searching for a pattern within a string and returning its starting position. Understanding how to use the PATINDEX() function and its syntax is essential for effective string searching and manipulation in SQL Server.