PATINDEX And CHARINDEX

PATINDEX

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.


Syntax:

PATINDEX ( '%pattern%', expression )

Eg: PATINDEX('%[0-9]%', @string)

PATINDEX for complex search




The following search will find all bikes which off-road yet
comfortable. 



Select  ProductNumber ,[Description]
from ProductDescription pd

where patindex( ‘%off-road%comfortable%’, pd.[Description]) > 0



 


  

  

ProductNumber

  

  

Description




 

  

  

BK-M18S-12

  

  

Suitable for any type of riding, on or off-road. Fits any
  budget. Smooth-shifting with a comfortable ride.




 

  

  

BK-M18S-21

  

  

Suitable for any type of riding, on or off-road. Fits any
  budget. Smooth-shifting with a comfortable ride.




 

CHARINDEX

Searches an expression for another expression and returns its starting position if found.

SYNTAX:

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )

Eg:CHARINDEX(' ',@String)


start_location
        It is optional.
If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expressionToSearch.