SQL fuzzy Query sorting problem

Learn a little bit of programming every day PDF ebook, video tutorial free download:
Http://www.shitanlife.com/code Requirements Description

• Query the data in the table with the specified keyword
• Exact match in first place
• Pre-match in second place
• End match placed in third place
• Middle match in fourth place

Build table

DROPTABLEIFEXISTS ' T_user '; create TABLE  ' T_user ' ( ' id ' varchar (20) not null,  Name ' varchar (20) DEFAULT null,  ' password ' varchar (20) default null,  PRIMARY key ( ' id ')) engine=innodb  DEFAULT Charset=utf8;               

Initial data

InsertInto T_userValues1,' Wang Feng ',' 888888 ');InsertInto T_uservalues (2, ' 王汪峰 ', insert into t_user values (3," Wang Feng Feng ",  888888 '); insert into t_user values (4, Ouyang Wang Feng ',  888888 '); insert into t_user values (5, ' Wang Wan PK ',  888888 ')                  

Direct like query

select * from t_user where name like ‘%汪峰%‘;

• 1

The result is:

It can be found that "Wang Feng" is not arranged in front of "Wang Wangfeng"

Auxiliary sorting using case

SELECT *From T_user Fwhere F.nameLike'% Wang Feng% 'Orderby (casewhen f.name =  ' Wang Feng ' then 1 when f.name  Like  Wang Feng% ' then 2when f.name like  '% Wang Feng ' then 3when f.name like "% Wang Feng% ' then 4 Span class= "Hljs-keyword" >else 0end) limit 0,50;            

The query results are:

Equal to the addition of a virtual column to assist in sorting;
First match the full = =, and then follow the rules to match the next data, you can get the results we want.

Learn a little bit of programming every day PDF ebook, video tutorial free download:
Http://www.shitanlife.com/code

SQL fuzzy Query sorting problem