SQL query to search for a record that has a certain text?

Question

How can I query for a record that has certain text input from the user?

For example in my table adapter function:

SELECT Word, Description, Example  
FROM WordLists  
WHERE (Word LIKE @SearchKey OR Description LIKE @SearchKey OR Example LIKE @SearchKey)

Obviously only record that has the exact text from certain input will be acquired from the DB. What I need to do is to get all records that contains input texts from user.

Answer 1

SELECT Word, Description, Example
FROM WordLists
WHERE ( (Word LIKE '%' + @SearchKey + '%') 
   OR (Description LIKE '%' + @SearchKey + '%') 
   OR (Example LIKE '%' + @SearchKey +'%') ) 

Answer 2

Another option is:

SELECT Word, Description, Example
FROM WordLists
WHERE ( Word || ' ' || Description || ' ' || Example ) LIKE ( '%' + @SearchKey + '%' )

This may (or may not) be more efficient, and it might yield some false positives where @SearchKey matches Word || ' ' || Description, this might be a good thing, it might not; but it's may be a bit more readable, according to your particular style.

Answer 3

Depending on the size of your dataset, you may want to try full text (if that's an option).

You could create a full text index across all 3 search-able columns which would also give you the ability to query a specific one (or combination) if the need arose - e.g. you want rows where the search text exists in the [Description] only.

if object_id('dbo.ft_example') is not null drop table dbo.ft_example;

create table dbo.ft_example (
    rowid int identity not null constraint pk_ft_example primary key,
    [Word] varchar(100),
    [Description] varchar(1000),
    [Example] varchar(500)
    );

insert  dbo.ft_example ([Word], [Description], [Example])
select  'blah blah cat', 'blah blah blah blah blah blah blah blah bird', 'blah blah blah blah fish' union all
select  'blah blah dog', 'blah blah blah blah blah blah blah blah cat', 'blah blah blah blah horse' union all
select  'blah blah camel', 'blah blah blah blah blah blah blah blah squid', 'blah blah blah blah horse' union all
select  'blah blah horse', 'blah blah blah blah blah blah blah blah cat', 'blah blah blah blah moose' union all
select  'blah blah fish', 'blah blah blah blah blah blah blah blah whale', 'blah blah blah blah bird' union all
select  'blah blah camel', 'blah blah blah blah blah blah blah blah squirel', 'blah blah blah blah kiwi' union all
select  'blah blah kiwi', 'blah blah blah blah blah blah blah blah bird', 'blah blah blah blah horse'; 

if exists( 
    select * 
    from sys.fulltext_indexes 
    join sys.tables 
    on sys.tables.object_id = sys.fulltext_indexes.object_id 
    where sys.tables.name = 'ft_example' 
    ) 
    drop fulltext index on ft_example; 
go 
if exists (select * from dbo.sysfulltextcatalogs where name = 'example_ft_cat') 
    drop fulltext catalog example_ft_cat; 
go 

create fulltext catalog example_ft_cat;
create fulltext index on dbo.ft_example ([Word], [Description], [Example]) 
    key index pk_ft_example on example_ft_cat; 
go

select  *
from    dbo.ft_example a
join    containstable(ft_example, ([Word], [Description], [Example]), 'bird') b
        on a.rowid = b.[key]