Comparison of charindex/patindex/like in SQL Server

I. test environment:

1. Database: SQL Server 2008

2. Test Table: 15000 records, char primary key, no other indexes

3. Test Field: ntext type, maximum Data Length: 12000

 

Ii. Test statement:

 

1. Select * From producttemp where productdesc like '% 192.168.70.236%'

2. Select * From producttemp where charindex ('192. 168.70.236 ', productdesc)> 0

3. Select * From producttemp where patindex ('% 192.168.70.236%', productdesc)> 0

 

Iii. Test results:

 

1. Efficiency Test Results: charindex> like> patindex, the efficiency difference is about 20%

2. Applicability Test results:

1) charindex requires the maximum displacement of the search results. After testing, the ntext field in sql2008 cannot exceed 3987; otherwise, 0 is returned;

2) patindex and like have no displacement restrictions;

3) patindex supports matching expressions and can apply regular expressions;

4) Like can be fuzzy matched using '% oldstring %;

5) charindex can only match fixed strings