Searching words in a database

I need to improve the search on a website which has a search box that only search开发者_如何学Goes for the exact same characters. If I type in hyperlink it will return everything starting with hyperlink but not anything such as contenthyperlink, _hyperlink, etc. Here is my sql query -

    select          O_ObjectID, 
                rtrim(O_Name) as O_Name
    from            A_Object
    where           O_Name like @NamePrefix + '%'
    order by        O_Name

Strictly speaking your query is correct, however what you're really looking for is "words starting with 'hyperlink'" which means there will be a space character or it will be the start of the text field.

select          O_ObjectID, 
            rtrim(O_Name) as O_Name
from            A_Object
where           O_Name like @NamePrefix + '%' OR O_Name like '% ' + @NamePrefix + '%'
order by        O_Name

note the added space character in '% ' + @NamePrefix + '%'

Your other option would be to use full text search which would mean your query would look like this:

select          O_ObjectID, 
            rtrim(O_Name) as O_Name
from            A_Object
where           CONTAINS(O_Name, '"'+ @NamePrefix + '*"')
order by        O_Name

and performance on this will be significantly faster as it will be indexed at a word level.

Use this

select          O_ObjectID, 
                rtrim(O_Name) as O_Name
    from            A_Object
    where           O_Name like   '%'+ @NamePrefix + '%'
    order by        O_Name

you need put % in both sides of your searching text to match with <anything>hyperlink<anything> .See how SQL like operator works http://www.w3schools.com/sql/sql_like.asp . But as Simos Mikelatos sujested above if you use full text search you can find matches by its slimier words also.