Keyword search

I am trying to implement a band search on my web site (concert listings) and would like it to behave a bit more intelligently than a standard match on the band name.

At the moment I have a stored procedure that just selects every show that features a band with exactly the same name as the search term. What I'm now trying to do is when the user enters a band name containing the '&' character I would also like to search using the word and 'and'. For example, if they search for 'Rise & Fall', they should get details on all shows featuring 'Rise & Fall' OR 'Rise And Fall'. Is it possible to do this within my stored procedure?

Yes it's possible.

In your sproc declare another variable and set it's value based on the search parameter, but using the t-sql replace function to replace the '&'. e.g. set @.altered_1p = replace(@.p1, "&", "and")

Then in the where clause of your search sproc use both the input search string and your modified search string ie ...where band_name = @.p1 or band_name = @.altered_p1

|||

Hey,

I'm trying this but it's not working. When I execute the procedure I get no results, but if I run the SQL manually and type in the expected values for the parameters I do get results. The T-SQL in my procedure is..

declare @.BandName1nvarchar(100)

declare @.BandName2nvarchar(100)

set @.BandName1=replace(@.BandName,'and','&')

set @.BandName2=replace(@.BandName,'&','and')

-- Insert statements for procedure here

if(@.BandName!=null)

select Show_Idfrom dbo.Playinginnerjoin dbo.Bandson Playing.Band_Id= Bands.Id

innerjoin dbo.Showson Playing.Show_Id= Shows.Idand Shows.Date>=getdate()

where((Bands.Namelike'%'+ @.BandName+'%')

or(Bands.Namelike'%'+ @.BandName1+'%')

or(Bands.Namelike'%'+ @.BandName2+'%'))

orderby Shows.Date;