SQL Server Multi Term Wildcard Search on Multiple Fields with Ranking

Question

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

• paparazzo: But the original is not the same

Answer 1

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)

• James Law: 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?
• Devasuran: 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.
• t3chb0t: @JamesLaw I suggest reading LIKE vs CONTAINS on SQL Server

Answer 2

I see no purpose in this statement

INSERT INTO @SearchTerms
SELECT @SearchText

If you get duplicate terms you should limit to distinct

• James Law: by inserting @SearchText into @SearchTerms I was looking for a match on the complete search string as well as the split components of it.
• paparazzo: If the full string matches then a component will match.
• James Law: 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.