trjhtr

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

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)

I see no purpose in this statement

INSERT INTO @SearchTerms
SELECT @SearchText

If you get duplicate terms you should limit to distinct