Notes on SQL multi condition fuzzy query, in, custom function (1)


Some problems encountered in the work about SQL query, sort out the records, and implement the environment sqlservice 2014

1、 On the same field, several ways to query multiple keywords

Basic grammar:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern


Id LastName FirstName
1 Adams John
2 Bush George
3 Carter Thomas


Query statement implementation:

1. Single keyword fuzzy query

        SELECT * FROM TbUser WHERE  LastName LIKE ‘%d%’   

2. Multiple keyword queries

Implementation 1: Select*


WHERE LastName like ‘%d%’   or   LastName LIKE ‘%e%’   or   LastName LIKE ‘%d%’  


Realization 2:SELECT TbUser.LastName,temp.m

FROMTbUser,(values(‘%a%’),(‘%b%’),(‘%c%’)) as temp(m)

FROM TbUser.LastName LIKE temp.m


3SELECT LastName 

FROM TbUser 

WHERE LastName LIKE any(array[‘%a%’,’%b%’,’%e%’])


Realization 4:SELECT LastName  

FROM TbUser  

WHERE LastName LIKE  ‘%[a|b|e]%’


2、 The disguised realization of in keyword in SQL

When there are many matching data, in needs to be compared one by one, which is inefficient. Constructing left join structure will greatly improve the efficiency.


SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

Implementation 1:



WHERE Id in (1,2,3,4)


Realize the second 

SELECT  x FROM TbUser  u

LEFT JOIN  TbDetails as d on u.Id=d.FkUserId

 WHERE d.FkUserId is not null



If another array needs in query, you can construct a temporary table for inner join query as follows


FROM bUser  u

INNER JOIN ( VALUES(‘a’),(‘b’),(‘c’),(‘d’),(‘e’) ) as  temp(m) ON u.LastName=temp.m