SQL Server PATINDEX() Function

In SQL Server, the PATINDEX() function is used to find the position of a pattern within a string. It is similar to the CHARINDEX() function, but allows for pattern matching to find a substring. This function returns the position of the first successful pattern match within the string. If no match is found, it returns 0.

Syntax

The syntax for the PATINDEX() function is as follows:

PATINDEX('%pattern%', expression)

Where pattern is the pattern to match and expression is the string in which to search for the pattern.

pattern can include wildcard characters % and _, where % matches any character (including zero or more characters) and _ matches a single character.

Use Cases

The PATINDEX() function is commonly used in situations where a pattern needs to be found within a string. For example, it can be used to find a specific word or character sequence within a string.

Examples

Here are two examples of the PATINDEX() function.

Example 1

SELECT PATINDEX('%world%', 'hello world')

In this example, we search for a string that contains the word “world”. The function returns a value of 7, because “world” appears at the 7th character position.

Example 2

SELECT PATINDEX('1%4', '1234')

In this example, we search for a string that starts with 1 and ends with 4. The function returns a value of 1, because the first character is “1”.

Conclusion

The PATINDEX() function is a useful function for searching for strings that contain a specific pattern. It uses pattern matching, making it easy to search for complex patterns. The function returns the position of the first successful match, and if no match is found, it returns 0.