FreeTextTable filter search string

Question

I am joining onto a freetexttable using a searchTerm parameter which is also used in a number of other freetexttables in the query.

I would like to remove the city name from this parameter if it exists in the current row. Trying to use replace like this:

freetexttable(Gigs, Name, REPLACE(@searchText, c.CityName, '')) gigkt 
ON g.GigID = gigkt.[Key]

results in a syntax error.

Is there other way I can do this?

Answer 1

You will first have to process @SearchText in a separate query as the freetext_string parameter for FreeTextTable has to be a string variable - I don't think thing the return of a function will do, anyway you're looking to replace possibly multiple citynames as 'c' is a table.

The example below will result in the string 'abc abc abc'

declare @T table
(
    name varchar(20)
)

insert into @T(name) values('belfast')
insert into @T(name) values('armagh')

declare @name varchar(100)

select @Name = 'abc belfast abc armagh abc'

select
    @Name = replace(@Name, t.Name, '')
from
    @T as t


select @Name