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