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

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

Example

DECLARE @Name varchar(30)

SET @Name  = 'rohatash kumar'

SELECT PATINDEX('%K%', @Name)

Output: 10

Example

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

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.

Example

DECLARE @Name varchar(30)

SET @Name  = 'Rohatash'

SELECT CHARINDEX('ta', @Name )

Output: 5

Example

In this example we use the preceding table.

select UserID, UserName, CompanyName, CHARINDEX ('ta', UserName) as patindexint

from [UserDetail] where CompanyName='MCN Solutions'

Output