I have a web page that allows users to search a Question & Answer database table (no its not StackOverflow). The web page accepts the search term from the user and passes it to a stored procedure as a parameter on a SQL Server 2005 database. That sproc does a SELECT on the table in question, using FREETEXT in the WHERE clause to search all columns in the table. Works great under most scenarios.
However, the user wants to search for question/answers that contain the dollar sign character ("$"). I can query the database directly and view the records, however these records cannot be viewed through the web site. Is there a special format the user can enter into the web site to make it work? I've tried using wildcard character, escape characters, single quotes, double quotes, square brackets, double dollar signs, etc. No luck. Ideally, we'd like to leave the sproc alone.
Here's the SQL from the sproc (@keyword is the sproc parameter captured from the web page):
SELECT question, answer1, answer2, answer3, answer4
FROM tQuestion
WHERE FREETEXT(*, @keyword)
Here's the SQL that I run that returns the rows we're looking for:
SELECT question, answer1, answer2, answer3, answer4
FROM tQuestion
WHERE question like '%$P%'
We need to use FREETEXT to search all columns at once for the search term. Is there a certain format the user can enter in the web site that will allow them to view these records?