Comparison of charindex/patindex/like in SQL Server

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.