Patindex Function and Charindex Function in SQL Server 2012

In this article, I will show you how to use the Charindex and Patindex functions to search text columns and character strings. These two functions are used to search, manipulate and parse character strings in SQL Server. Patindex can use wildcard characters, but charindex cannot use wildcard characters. So let's take a look at a practical example of how to use the Charindex and Patindex functions in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio. There are some simple things to do that are described here.

Patindex() Function in SQL Server

Patindex function is similar to the like operator. The Patindex function returns an int. The Patindex function returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found. Patindex can use wildcard characters. The Patindex function operates on char, nchar, varchar, nvarchar, text, and ntext data types only.

Syntax

The Patindex function takes two arguments:

PATINDEX ('%pattern%', exp)

Pattern - The % character must precede and follow the pattern. The pattern argument is an expression of types that can be implicitly converted to nchar, nvarchar, or ntext.

Expression - An expression is a column that is searched to find the specified pattern. The expression argument can be nchar, nvarchar, or ntext. The Patindex function returns an int.

Example

The table looks as in the following:

Using Patindex function

DECLARE @Name varchar(30)
SET @Name = 'rohatash kumar'
SELECT PATINDEX('%K%', @Name)

Output: 10

In this example, we use the preceding table.

Select UserID, UserName, CompanyName, PATINDEX ('%ns%', UserName) as patindexint
from [UserDetail] where CompanyName='SLIT'

Output

Charindex() function in SQL Server

The Charindex function does not provide wildcard characters. The Charindex function is similar to the like operator and Patindex function. The Charindex function also returns an integer. The Charindex function operates on char, nchar, varchar, nvarchar, text, and ntext data types only. The charindex function returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found.

Syntax

CHARINDEX (exp1, exp2)

Expression1 - Exp1 is the string of characters to be found in exp2.

Expression2 - Exp2 is the position where the Charindex function will start looking for exp1 in exp2. The charindex function returns an integer value.

DECLARE @Name varchar(30)
SET @Name = 'Rohatash'
SELECT CHARINDEX('ta', @Name )

Output:5

In this example we use the preceding table.

select UserID, UserName, CompanyName, CHARINDEX ('ta', UserName) as patindexint
from [UserDetail] where CompanyName='MCN Solutions'

Output

2 Comments

Join the conversation! Your thoughts help the community grow.

pavan kumar7y ago
CHARINDEX() accepts a third argument which allows you to specify the start position of the search. PATINDEX() doesn’t.
Ani10y ago
thanks for the clear explanation. Just to bring to your notice in the Charindex function example you named the charindex column as patindexint.