Comparison of charindex/patindex/like in sqlserver

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