CHARINDEX and PATINDEX functions

We can use either CHARINDEX or PATINDEX to search in TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify.

Both functions take two arguments. With PATINDEX, 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. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.

The CHARINDEX function has the following syntax:

CHARINDEX (string_expression, target_expression [, start_location ])

Here an optional integer parameter start_location defines a position in a string_expression, where searching a target_expression starts from. If this argument is omitted, a searching starts from the beginning of a string_expression. For example, the query

  Console

SELECT name 


FROM Ships 


WHERE CHARINDEX('sh', name) > 0

will output the ships that has the sequence of characters "sh" in its names. Here we use the fact that if a substring to be found is not found, the function CHARINDEX returns 0. The result of executing the query will contain the following ships:

name Kirishima Musashi Washington

We should note that if a target_expression or string_expression is NULL, the result of the function is also NULL.

The next example determines the positions of the first and second occurrences of the character "a" in the name of the ship "California".

  

SELECT CHARINDEX('a',name) first_a, 


 CHARINDEX('a', name, CHARINDEX('a', name)+1) second_a 


FROM Ships 


WHERE name='California'

Pay attention that in determining of the second occurrence the starting position, used in function, is the position of a character next to the first "a" - CHARINDEX('a', name)+1. The propriety of the result - 2 and 10 - can be easily checked.

The PATINDEX function has the following syntax:

PATINDEX ('%pattern%' , string_expression)

The main distinction of this function from CHARINDEX is that a search string may contain wildcard characters - % and _. Leading and ending characters "%" are required. For, example, usage of this function in the first example will look like

 

SELECT name 


FROM Ships 


WHERE PATINDEX('%sh%', name) > 0

And there's the way to find the names of the ships that contain the sequence of three characters, first and last of which is "e":

 

SELECT name 


FROM Ships


WHERE PATINDEX('%e_e%', name) > 0

The result of executing of this query looks like this:

Name Revenge Royal Sovereign