In terms of functionality, this is very similar to CHARINDEX in that it simply looks for a pattern within a string.
However, where it differs is that PATINDEX requires you to use wildcard characters as part of your search, something that CHARINDEX will not, but it is less powerful in some ways in that it does not allow you to specify a starting point and therefore will only ever find the first occurrence of a pattern in a string.
Here are some uses along with showing the crossover between itself and CHARINDEX when performing a simple search…
PATINDEX and CHARINDEX
declare @text varchar(40)
set @text = 'bobthefish'
select PATINDEX('%fi%', @text)
declare @text varchar(40)
set @text = 'bobthefish'
select CHARINDEX('fi', @text, 1)
Finding a wildcard pattern
This is an incredibly useful technique to use if crudely verifying an email address as you can us it to check for "@" followed by "." within a string.
declare @text varchar(40)
set @text = 'bobthefish'
select PATINDEX('%t%i%', @text)
Finding a character in a number
declare @text varchar(40)
set @text = 'bobthe8fish'
select PATINDEX('%[0-9]%', @text)
Finding a number in a string
declare @text varchar(40)
set @text = '987b654321'
select PATINDEX('%[a-z]%', @text)