Search for acronym

[sharonc]

Using ASP and SQL Server.

Is it possible to search a field using an acronym such as BFP and find Boiler Feed Pump without hardcoding BFP into the table?

 

[damber]

Although I like to use the CONTAINS predicate for matching search inputs to table content, it isn't going to cut it for this one; not as you've described it anyway.

There are some technical 'fudges' you can do to try and do it with the existing table structure (no changes to the DB), though I can't think of one that is foolproof, and will be potentially unreliable/slow.

For example, assuming you want to ONLY search using acronyms, you could send the Acronym to a Stored procedure, this could then use a User Defined Function when comparing the Acronym to the field. The UDF would take the first character of each word and output as the string to be compared with.

If you have a variable search term entered - i.e. it could be an acronym, it could not be, then this will make your life even more complicated - how do you tell if it is an acronym? Or do you blindly run both types of searches ?

Personally, I wouldn't implement the above, as it's very unreliable. Why ? Because users won't necessarily know the exact acronym for the phrase stored in your db. Some acronyms are first-letter-abbreviations, such as TLA (Three Letter Acronym) or ASP (Active Server Pages), but others include variable length abbreviations, such as MSc (Masters of Science) or Xmas (Christmas). Take MSWinXPPro - this would be "Microsoft Windows XP Professional" - how would you code that with any consistency and simplicity? Add to that the users interpretation of an acronyms presentation - e.g. (ASP or A.S.P or A.S.P. or ASPs or ASP's or asp or A S P) I'm sure it can be done, but it would be quite slow and probably require extensive knowledge of Regular Expressions.

A better way is to use a synonym reference for the phrase - either inline of the same table (i.e. in row) or as an external table - related one to many - which is a more normalised approach. That way you can have any variation on the Phrase, and performance will be much better too.

There may be a way that is somehow escaping me, but it seems too irregular an input pattern to match consistently with simple/elegant code.

Either way good luck.

A smile is worth a thousand kind words. So smile, it's easy!

 

[Sheco]

You might find partial success with a wildcard LIKE search:

WHERE ThisField LIKE 'B% F% H%'

That wouldn't handle anything besides the most basic straightforward acronyms.