This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing the use of CONTAINS for matching the patterns in SQL Server.
We know about the LIKE predicate which we are using for a specific type of pattern matching. SQL Server Full-Text Search Index stores the different patterns of the defined column.
We can employ CONTAINS on the column of Full-Text Search and can search different patterns.
Please check the below demonstrations:
Create a table with sample records:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_Employee (empid int not null, ename varchar(20) not null, salary int) GO INSERT INTO tbl_Employee VALUES (1,'abc',25000),(2,'xyz',30000),(3,'svq',40000) ,(4,'cvq',26000),(5,'rfq',70000),(6,'opq',45000) GO |
Create a Full Text Search Index:
|
1 2 3 4 5 6 7 |
CREATE UNIQUE INDEX uk_empid ON tbl_Employee(empid); CREATE FULLTEXT CATALOG ft AS DEFAULT; CREATE FULLTEXT INDEX ON tbl_Employee(ename) KEY INDEX uk_empid WITH STOPLIST = SYSTEM; |
Use CONTAINS, for performing FULL Text Search:
Check the below sample use of CONTAINS, If you want to use the same thing using LIKE then we have to write LIKE separately for each pattern.
|
1 2 |
SELECT *FROM [dbo].[tbl_Employee] WHERE CONTAINS(ename, '"a*" OR "s*" OR "cv*"') |
Result:
|
1 2 3 4 5 |
empid ename salary ----------- -------------------- ----------- 1 abc 25000 3 svq 40000 4 cvq 26000 |
