SQL multi-field fuzzy query optimization

In the actual development often encounter the same keyword need to multi-field fuzzy query, such as a user table in the Input keyword query may be to the user name, user name, contact phone fields such as fuzzy query

If written as: where UserName like '% ' keyword% ' or fullName as '% ' keyword% ' or mobile kind '% keyword% ' This way will result in very low performance, hundreds of thousands of data may be very long before the world to query out, in order to solve this problem , you can put these fields together and then string search the stitching string, so that the performance will be very high elevation;

This can be written in SQL Server: where charindex (' keyword ', ISNULL (UserName, ') +isnull (FullName, ') +isnull (Mobile, ') >0

This can be written in Orace: Where InStr (NVL (UserName, ") | | NVL (FullName, ") | | NVL (Mobile, '), ' keywords ') >0

The above is written for the same keyword, and can be written if the keyword is different

This can be written in SQL Server: where CHARINDEX (' keyword 1 ', userName) >0 or charindex (' keyword 2 ', fullName) >0 or charindex (' keyword 3 ', Mobile) >0

This can be written in Orace: Where InStr (userName, ' keyword 1 ') >0 or InStr (fullName, ' keyword 2 ') >0 or InStr (Mobile, ' keyword 3 ') >0

Note that you must make a null judgment when stitching the string, otherwise the result of NULL concatenation will be NULL if there is one field.

SQL multi-field fuzzy query optimization