Published: 3rd May 2013
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…


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)

NB: Comments will only appear once they have been moderated.