Home > SQL Server, T-SQL > Find the pattern in a string using PATINDEX() function

Find the pattern in a string using PATINDEX() function


PATINDEX() function is used to find a pattern in the text and returns the starting position of the first occurrence in the text. There are two arguments in this function Pattern and character expression.

Syntax

PATINDEX ( ‘%pattern%’ , expression or text or string)

The pattern must have % symbol in the front and the end of the search pattern string. Except, if you want to search the first character or last character in the text. If the pattern is invalid then the output of the function is 0 (Zero). Otherwise, it returns Int data value or BigInt data value if the text data type is varchar(max) or nvarchar(max).

Points to remember

1. If the pattern or text is NULL then the output is also a NULL.

2. Pattern search is collation sensitive. So, you may expect different result with different collation setting. We can also use explicit collation using COLLATE.

3. Pattern uses wild card characters for searching. So, we can use this as a LIKE clause. Please remember that % symbol should be used in the pattern properly.

Example pattern search with different  scenario

1. Search for 2012 content in the text

Select PATINDEX(‘%2012%’, ‘SQL Server 2012’)

Output

12

2. Search for character x at the end of the text

Select PATINDEX(‘%x’, ‘CharIndex’)

Output

9

3. Search for character p at the beginning of the text

Select PATINDEX(‘P%’, ‘PatIndex’)

Output

1

4. Search for a string which is started with T and end with t. But, in between T and t there can be any character but the length should be 2 characters.

SELECT PATINDEX ( ‘%T__t%’, ‘This is a Test’ COLLATE Latin1_General_BIN)

5. Search with pattern string empty

select PATINDEX(”, ‘PatIndex’)

Output

0

6. Search NULL in the text

select PATINDEX(NULL, ‘SQL Server Rider’)

Output

NULL

We can also use wildcard characters to find the patterns such as [a-z], [0-9] and etc.

There is one more string function CHARINDEX() in SQL Server that does exactly the same but without pattern. Learn about the function here.

Readers can write your thoughts about the use of  PATINDEX function in the comment section.

Thanks for reading.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: