loading..
Русский    English
20:09
go

CHARINDEX and PATINDEX functions

The CHARINDEX function has the following syntax:

  1. 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
Execute
  1. SELECT name 
  2. FROM Ships 
  3. 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".

Console
Execute
  1. SELECT CHARINDEX('a',name) first_a, 
  2.  CHARINDEX('a', name, CHARINDEX('a', name)+1) second_a 
  3. FROM Ships 
  4. 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:

  1. 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

Console
Execute
  1. SELECT name 
  2. FROM Ships 
  3. 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":

Console
Execute
  1. SELECT name 
  2. FROM Ships
  3. WHERE PATINDEX('%e_e%', name) > 0

The result of executing of this query looks like this:

Name
Revenge
Royal Sovereign

Suggested exercises: 136
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.