Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


PATINDEX
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…

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)


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

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.