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

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.

See the example

SELECT PATINDEX('%K%','Bageshkumarsingh') as k_position

  

 It returns the position of starting index for characters 'k' in the specified string. 

We can use wildcard character.

Wildcard	Explanation
%	Allows you to match any string of any length (including zero length)
_	Allows you to match on a single character
[ ]	Allows you to match on any character in the [ ] brackets (for example, [abc] would match on a, b, or c characters)
[^]	Allows you to match on any character not in the [^] brackets (for example, [^abc] would match on any character that is not a, b, or c characters)