Dealing with Ampersands &

[samurrai]

We have a large sequel server database that acts as the backend of a recruiting app (java-based). We have the ability to search documents saved in the system. But, at present users can not search docs for words with special characters like the ampersand (&) character. There's no way to execute a control character on the front end. A suggestion was to include the ampersand as a "searchable" item on the backend. We're told that this would be an addition to a stored procedure or a system table. Has anyone dealt with anything similar? Looking at the database we're not certain exactly where to add this entry or if this could have negative effects.

 

[RiverGuy]

Well, if you have a table in your database like the following:

StringTranslations
-----------------
OriginalString     TranslatedString
--------------     ----------------
*ampersand*        &
*percentsign*      %

Then you could have a stored procedure as follows:

CREATE PROCEDURE p_LookUpData
@LookupString VARCHAR(100)

AS

SELECT @LookupString = ISNULL(TranslatedString, @LookupString) FROM StringTranslations WHERE OriginalString = @LookupString

SELECT * FROM SomeTable WHERE SomeColumn LIKE '%' + @LookupString  + '%'

You could then code your application to supply the "OriginalString" values to your stored procedure instead of the special characters.