The PATINDEX function

To properly understand this function let’s take an example.

Write a query in SQL Server to return the value from a table field starting from any number.

Eg. Suppose there is a field Address in the table Employee.

Now if the address is:  SDF # E-15/21/25, NSEZ, Noida – 201305, India
The query should return: 15/21/25, NSEZ, Noida – 201305, India

The Answer is….

DECLARE @address varchar(50)
SET @address='SDF # E-15/21/25, NSEZ, Noida – 201305, India'
DECLARE @start_expression int
SELECT @start_expression= Patindex('%[0-9]%',@address)
SELECT SUBSTRING ( @address,@start_expression,LEN(@address)-@start_expression+1)

The PATINDEX function returns the starting position of a character or string of characters within another string, or expression. The PATINDEX has additional functionality over CHARINDEX. PATINDEX supports wildcard characters in the search pattern string. This makes PATINDEX valuable for searching for varying string patterns. The PATINDEX command takes the following form:

PATINDEX ( '%pattern%' , expression )

Where "pattern" is the character string you are searching for and expression is the string in which you are searching. Commonly the expression is a column in a table. The "%" sign is needed on the front and back of the pattern, unless you are searching for the pattern at the beginning and/or ending of the expression.
Download Article