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

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