How to Use the PATINDEX() Function in SQL Server: A Detailed Guide

PATINDEX function returns the starting position of the first occurrence of a pattern in a specified expression. It takes two arguments, the pattern to be searched and the expression. PATINDEX() is simial to CHARINDEX(). With CHARINDEX() we cannot use wildcards, where as PATINDEX() provides this capability. If the specified pattern is not found, PATINDEX() returns ZERO.

Syntax:

PATINDEX('%Pattern%', Expression)

Code:

 

Select Email, PATINDEX('%@aaa.com', Email) as FirstOccurence 
from tblEmployee
Where PATINDEX('%@aaa.com', Email) > 0

Prerequisite Code

CREATE TABLE TableEmployee(
	FirstName varchar(50),
	LastName varchar(50),
	Email varchar(50)
)

INSERT INTO TableEmployee VALUES('Rambo', 'Azmi', 'Rambo@aaa.com')
INSERT INTO TableEmployee VALUES('Azam', 'Ali', 'Azam@aaa.com')
INSERT INTO TableEmployee VALUES('Inza', 'Hoque', 'Rambo@aaa.com')
INSERT INTO TableEmployee VALUES('Jaman', 'Sk', 'Jaman@aaa.com')
INSERT INTO TableEmployee VALUES('Samser', 'Alam', 'Samser@aaa.com')

SELECT * FROM TableEmployee