Find The Model For SQL Server Using PATINDEX () And CHARINDEX ()

Although these features are not commonly used, is very powerful in the search for a reason and return the location of the text / string / model. By understanding the functionality of the string functions, we will be able to understand the difference too.

PATINDEX ()

The search function of a pattern in a string / expression and returns the starting position of the first occurrence of the pattern. It works similarly to the operator, as in any application.

The general syntax is

PATINDEX ('% pattern%', expression)

You may have already noticed the '%' symbols used in the model. These are important, as I mentioned before, using the same syntax used for research by the LIKE operator.

Choose PATINDEX ('% System%', description) index location, description

From dbo.TestTable

CHARINDEX ()

CHARINDEX () function is similar to PATINDEX (). However, CHARINDEX search for the string expression specified in the start position (Start loction default is 1). If you have already noted above, we can begin to determine the position of PATINDEX ()

The general syntax is

CHARINDEX (expression1, expression2 [, start_location])

An example of the same data set used earlier would look like

Select CHARINDEX ("System", description) index position, description

from dbo.TestTable

We can also start position indlude. Below is a sample search function in the string from the position of three

Select charindex ("System", description, 3) indexlocation Description

from dbo.TestTable

Finally, the main difference between the PATINDEX () and CHARINDEX () is the ability to CHARINDEX To find a specific location.