# SQL Server Multi Term Wildcard Search on Multiple Fields with Ranking

I've built up this search query for searching airport locations on multiple fields, but I don't particularly like the way it works in practice. Even though it is fairly cool. I'll be reverting back to the original version, however I'd like to hear people's thoughts on the approach and possible alternatives.

DECLARE @SearchText nvarchar(255)
DECLARE @SearchTerms table(Term nvarchar(255))

INSERT INTO @SearchTerms
SELECT      @SearchText

INSERT INTO @SearchTerms
SELECT value FROM STRING_SPLIT(@SearchText, ' ')

;WITH cte AS (
SELECT      LocationId,
Name,
Locality,
Country,
ICAO,
IATA,
Usage,
RunwayLength,
RunwaySurface,
CASE WHEN l.Name LIKE st.Term + '%' THEN LEN(st.Term) ELSE 0 END AS StartsWithName,
CASE WHEN l.ICAO LIKE st.Term + '%' THEN LEN(st.Term) ELSE 0 END AS StartsWithICAO,
CASE WHEN l.IATA LIKE st.Term + '%' THEN LEN(st.Term) ELSE 0 END AS StartsWithIATA,
CASE WHEN l.Name LIKE '%' + st.Term + '%' THEN LEN(st.Term) ELSE 0 END AS NameMatch,
CASE WHEN l.ICAO LIKE '%' + st.Term + '%' THEN LEN(st.Term) ELSE 0 END AS ICAOMatch,
CASE WHEN l.IATA LIKE '%' + st.Term + '%' THEN LEN(st.Term) ELSE 0 END AS IATAMatch
FROM        [dbo].[Locations] l with (nolock)
INNER JOIN  @SearchTerms st ON l.Name LIKE '%' + st.Term + '%'
OR          l.ICAO LIKE '%' + st.Term + '%'
OR          l.IATA LIKE '%' + st.Term + '%'
),
cte2 AS (
SELECT          cte.LocationId,
cte.Name,
cte.Locality,
cte.Country,
cte.ICAO,
cte.IATA,
cte.Usage,
cte.RunwayLength,
cte.RunwaySurface,
SUM(cte.StartsWithName) AS StartsWithName,
SUM(cte.StartsWithICAO) AS StartsWithICAO,
SUM(cte.StartsWithIATA) AS StartsWithIATA,
SUM(cte.NameMatch) AS NameMatch,
SUM(cte.ICAOMatch) AS ICAOMatch,
SUM(cte.IATAMatch) AS IATAMatch
FROM            cte
GROUP BY        cte.LocationId,
cte.Name,
cte.Locality,
cte.Country,
cte.ICAO,
cte.IATA,
cte.Usage,
cte.RunwayLength,
cte.RunwaySurface
)
SELECT          cte2.*
FROM            cte2
ORDER BY        cte2.StartsWithName DESC,
cte2.StartsWithICAO DESC,
cte2.StartsWithIATA DESC,
cte2.NameMatch DESC,
cte2.ICAOMatch DESC,
cte2.IATAMatch DESC,
cte2.Name ASC,
cte2.ICAO ASC,
cte2.IATA ASC


Here's the original if you're interested...

DECLARE @SearchText nvarchar(255)

WITH cte AS (
SELECT      LocationId,
Name,
Locality,
Country,
ICAO,
IATA,
Usage,
RunwayLength,
RunwaySurface,
CASE WHEN l.Name LIKE @SearchText + '%' THEN 1 ELSE 0 END AS StartsWithName,
CASE WHEN l.ICAO LIKE @SearchText + '%' THEN 1 ELSE 0 END AS StartsWithICAO,
CASE WHEN l.IATA LIKE @SearchText + '%' THEN 1 ELSE 0 END AS StartsWithIATA,
CASE WHEN l.Name LIKE '%' + @SearchText + '%' THEN 1 ELSE 0 END AS NameMatch,
CASE WHEN l.ICAO LIKE '%' + @SearchText + '%' THEN 1 ELSE 0 END AS ICAOMatch,
CASE WHEN l.IATA LIKE '%' + @SearchText + '%' THEN 1 ELSE 0 END AS IATAMatch
FROM        [dbo].[Locations] l with (nolock)
WHERE       l.Name LIKE '%' + @SearchText + '%'
OR          l.ICAO LIKE '%' + @SearchText + '%'
OR          l.IATA LIKE '%' + @SearchText + '%'
)
SELECT          cte.LocationId,
cte.Name,
cte.Locality,
cte.Country,
cte.ICAO,
cte.IATA,
cte.Usage,
cte.RunwayLength,
cte.RunwaySurface
FROM            cte
ORDER BY        cte.StartsWithName DESC,
cte.StartsWithICAO DESC,
cte.StartsWithIATA DESC,
cte.NameMatch DESC,
cte.ICAOMatch DESC,
cte.IATAMatch DESC,
cte.Name ASC,
cte.ICAO ASC,
cte.IATA ASC

• But the original is not the same – paparazzo Feb 8 '18 at 18:55

In this case, you should be using Full Text Search to accomplish your goal.

Try using the CONTAINS predicate as in CONTAINS ((l.Name, l.ICAO, l.IATA), @SearchText)

• What advantages does using full text search give me over my existing approach? I agree it's a nicer syntax but it appears to achieve the same results? – James Law Jan 10 '18 at 12:06
• In any case, the results you expect should be same. Yes, looking at it might appear that it is all the same. However, full text search (FTS) does carry multiple benefits. They are: 1) Enables you to search by keywords (creates index based on keyword) and also enables you to search by linguistic which is similar to fuzzy search technique, 2) High performance benefit: solid performance results even if you apply in VLDB data, 3) easier to read and maintain the code base. – Devasuran Jan 10 '18 at 12:58
• @JamesLaw I suggest reading LIKE vs CONTAINS on SQL Server – t3chb0t Feb 9 '18 at 5:40

I see no purpose in this statement

INSERT INTO @SearchTerms
SELECT      @SearchText


If you get duplicate terms you should limit to distinct

• by inserting @SearchText into @SearchTerms I was looking for a match on the complete search string as well as the split components of it. – James Law Feb 9 '18 at 9:17
• If the full string matches then a component will match. – paparazzo Feb 9 '18 at 9:21
• You're right, but don't forget the query ranks results and a full string match should rank higher. I appreciate your two cents but I'm not asking for someone to pull this apart - I was asking for feedback on the approach/concept. – James Law Feb 9 '18 at 9:24