?
Solved

What's the difference between CHARINDEX and PATINDEX?

Posted on 2012-08-10
4
Medium Priority
?
2,413 Views
Last Modified: 2012-08-10
Hi All

Question for you:  What's the difference between CHARINDEX and PATINDEX?
The difference is not clear to me.

When I run the below script it returns the same values:

Declare @str varchar(100) = 'abcdefghigklmnopqrstuvwxyz_e'

/*
CHARINDEX
http://msdn.microsoft.com/en-us/library/ms186323.aspx
Searches an expression for another expression and returns its starting position if found. 
*/

-- Select the first e in a string
SELECT CHARINDEX('ef', @str)

-- Select the first e in a string, starting at the 10th character
SELECT CHARINDEX('e', @str, 10)

/*
PATINDEX
http://msdn.microsoft.com/en-us/library/ms188395

Turns the starting position of the first occurrence of a pattern in a specified expression, 
  or zeros if the pattern is not found, on all valid text and character data types. 
*/
  
SELECT PATINDEX('%efg%', @str)

Open in new window

They both return bigint's that are the starting values.

Full disclosure:  I'm studying for the 70-433 exam, and this is not a work-related question.
0
Comment
Question by:Jim Horn
4 Comments
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 500 total points
ID: 38281433
0
 
LVL 7

Assisted Solution

by:twol
twol earned 500 total points
ID: 38281596
The PATINDEX function allows for using wildcard syntax in the pattern being searched for, where as the CHARINDEX function does not support wildcard character searches. Let's look at each one of these functions in a little more detail.

A good page to look at is here:
Differences between CharIndex and PatIndex and how to use them.
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 38281947
More generally, PATINDEX searches for a pattern, while CHARINDEX searches for a substring.

So, it's not just wild cards.  For example, you can use PATINDEX to find the starting position for the first substring that consists of three consecutive digits:

PATINDEX('%[0-9][0-9][0-9]%', SomeColumn)

Sort of like "Regular Expressions Lite" :)
0
 
LVL 66

Author Closing Comment

by:Jim Horn
ID: 38282106
>PATINDEX can use wildcard characters, but CHARINDEX cannot.
Okay.  That I saw, but was wondering if it was any other difference.  Now I know.   Thanks.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question