CHARINDEX and PATINDEX functions

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

SELECT name
FROM Ships
WHERE CHARINDEX('sh', name) > 0

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:

Suggested exercises: 136