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