Performing an ASP.NET database search with StartsWith keyword

Question

I have a query. I am developing a site that has a search engine. The search engine is the main function. It is a business directory site.

A present I have basic search sql that searches the database using the "LIKE" keyword.

My client has asked me to change the search function so instead of using the "Like" keyword they are after something along the lines of the "Startswith" keyword.

To clarify this need here is an example. If somebody types "plu" for plumbers in the textbox it currently returns, e.g.,

1. CENTRE STATE PLUMBING & ROOFING
2. PLUMBING UNLIMITED

The client only wants to return the "PLUMBING UNLIMITED" because it startswith "plu", and doesn't "contain" "plu"

I know this is a weird and maybe silly request, however does anyone have any example SQL code to point me in the right direction on how to achieve this goal.

Any help would be greatly appreciated, thanks...

Answer 1

how about this: SELECT * FROM MyTable WHERE MyColumn LIKE 'PLU%'

please note that the % sign is only on the right side of the string
example in MS SQL

Answer 2

Instead of:

select * from professions where name like '%plu%'

, use a where clause without the leading %:

select * from professions where name like 'plu%'

Answer 3

LIKE won't give you the performance you need for a really effective search. Looking into something like Lucence or your engine's Full Text Search equivalent.