SQL Server like, CHARINDEX, PATINDEX (reprint)

SQL Server like, CHARINDEX, patindex 1, Environment Introduction test environment SQL2005 test data 200W  2, Environment preparation 2.1 tables CREATE TABLE [dbo]. [Depratments] (        [dep_id] [int] not null,        [dep_name] [varchar] (a) COLLATE Chinese_ Prc_ci_as not NULL) on [primary]2.2 Creating data CREATE PROCEDURE ins_depratmentsas        DECLARE @n int;        DECLARE @title varchar (+);        set @n =1;        Set @ti Tle= ';begin        while @n<2000000        begin        &N Bsp      --Set @title = (CAST (Floor (rand () * 6) as int.) =5 Then ' department manager ' else ' staff ' end;                INSERT INTO depratments (dep_id,dep_name) VALUES (@n, ' development ' +cast (@n as Var char));               --INSERT into employees values (@n, ' Liu Bei ' +cast (@n as varchar ), ' Male ', @title,                       78000, ' 11110333x ' +cast (@n as varchar), @n,getdate ());                set @[email protected]+1;        endend2.3 Execute        exec ins_depratments 3, the query set STATISTICS IO ONset STATISTICS time on  with a percent semicolon around scene 3.1 Goselect COUNT (*) from depratments where dep_name like '% development 1000% '; go  select count (*) from depratments where Charinde X (' dev ', dep_name) >0;goselect count (*) from depratments where PATINDEX ('% development 1000% ', dep_name) >0;go no index condition Charindex > Patindex > like        CPU time = 4391 milliseconds, occupied time = 5322 milliseconds.         CPU time = 3812 milliseconds, elapsed time = 4690 milliseconds.         CPU time = 4047 milliseconds, elapsed time = 5124 milliseconds. Indexed case CHARINDEX > Patindex > like       cpu time = 4297 milliseconds, occupied time = 4535 milliseconds.       &NBSP;CPU time = 3844 milliseconds, occupied time = 4024 milliseconds.       &NBSP;CPU Time = 4219 milliseconds, occupied time = 4351 milliseconds. Conclusion: The current use of percent semicolon (%string%), ①charindex performance slightly better, like, patindex performance similar; ② index fails in this case  3.2 percent semicolon in the following query set STATISTICS IO ONset Statistics Time On goselect count (*) from depratments where dep_name like ' develop 1000% '; goselect count (*) from depratment s where charindex (' Dev ', dep_name) >0;goselect count (*) from depratments where PATINDEX (' Development 1000% ', Dep_name) >0; go  no index patindex > like > charindex        CPU time = 844 milliseconds, occupied time = 1465 milliseconds.         CPU time = 3875 milliseconds, elapsed time = 3914 milliseconds.         CPU time = 968 milliseconds, elapsed time = 969 milliseconds.   Case with index like > Patindex > charindex        CPU time = 0 milliseconds, occupied time = 18 milliseconds         CPU time = 3766 milliseconds, elapsed time = 4026 milliseconds.         CPU time = 937 milliseconds, elapsed time = 983 milliseconds. Conclusion: Without index, PATINDEX performance is the best, the performance of the charindex is 4 times times the index of the case, like the best properties   Summary: ① index only applies to the percent semicolon in the following case (string%) ② in the case of both the percent sign before and after the charindex performance of the best ③ percent sign in the following query, no index, PATINDEX performance best  

3) Patindex supports matching expressions, you can apply regular, select COUNT (*) from depratments where Patindex ('%[1-5]', dep_name) >0, and others such as: [^e]: Does not contain "E" of ....

4) Like can use '%oldstring% ' for fuzzy matching;

5) charindex can only match fixed string