SQL Server: PATINDEX String Functions in SQL Server



PATINDEX: The PATINDEX functions return the starting position of a pattern you specify.

You must include percent signs before and after the pattern,

Unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column or text.

Syntax:

PATINDEX ( '%pattern%', expression )

Pattern: Pattern is a character expression that contains a sequence of the characters. Wildcard characters can be used.

Expression: The expression that is searched for the specified pattern.

Example:

SELECT PATINDEX('%e____le%', 'This example of the Patindex');



Note: PATINDEX works just like LIKE, so you can use any of the wildcards. You do not have to enclose the pattern between percents. PATINDEX('a%', 'abc') returns 1 and PATINDEX('%a', 'cba') returns 3.

Unlike LIKE, PATINDEX returns a position, similar to what CHARINDEX does.

Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...