Published: Oct 28, 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)

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron