top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to Work with PATINDEX() funciton in sql server 2008?

0 votes
413 views
How to Work with PATINDEX() funciton in sql server 2008?
posted Mar 23, 2016 by Jayshree

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

PATINDEX () is the one of the most commonly used functions of SQL Server. It is used to find out the position of any specific pattern in a given string. It takes three parameters; first is the pattern whose position is to be find, second is the string in which the pattern’s position has to be find and third parameter is the starting location to find out the pattern. Third parameter is the optional one. When it is given, the search of the pattern will be then started from the index greater than the start location.

It is similar to the function CHARINDEX(). The only difference between these two functions is that in PATINDEX(), we can use wildcard characters in the pattern given to be find. ‘%’ is used to represent any string and ‘_’ is used to represent any character.

Syntax:

PATINDEX (pattern, superstring, start_location)

It can be used only with the SELECT command. The syntax for using it with SELECT command

SELECT

PATINDEX (pattern, superstring, start_location)

AS

Pattern Position

Example:

SELECT

PATINDEX (‘%amp%’, ‘It is an example’)

AS

Pattern Position

Output:

String Position


12

Another example to show, if string with different characters has to be searched.

Example:

SELECT

PATINDEX (‘%[e,E]xample%’, ‘the example to obtain the result’, 10)

AS

String Position

Output:

String Position


5

Here, The result will be shown for both strings; having example or Example.

answer Mar 23, 2016 by Shivaranjini
...