Need some help in searching

Dear ASP.NET

How can I find records that contain a STRING from some (more than one) other fields ?

for example, I have:

Name_First = "aaa"
Name_Middle = "bbb"
Name_Last = "ccc"

Key_Words = "aaa,bbb,ccc"
(includes all values - comma separated)

How can I do the SELECT so that when I search for "bbb" on Key_Words I will get my record ?

Should I use "LIKE %aaa%" or something like this ?
(should I keep the comma separators ?)

Thanks in advance, Yovav.You can use like % for sure. Generally when I have searching functions where a user can enter one or more fields

I apppend each field in the SQL string as I build it. So for example mine may look something like the following:

DECLARE @.SQLWHERE varchar(1000)
SET @.SQLWHERE = ''

IF @.FirstName IS NOT NULL
SELECT @.SQLWHERE = ' WHERE FirstName ' + ' LIKE ''' + '%' + @.FirstName + '%'''

IF @.LastName IS NOT NULL
BEGIN
IF @.SQLWHERE = ''
SELECT @.SQLWHERE = @.SQLWHERE + ' WHERE P.LastName ' + ' LIKE ''' + '%' +
@.LastName + '%'''
ELSE
SELECT @.SQLWHERE = @.SQLWHERE + ' AND P.LastName ' + ' LIKE ''' + '%' + @.LastName + '%'''
END

And you can keep doing that type of logic for as many as you would like,

Another option is not to include any "%" at all and then indicate on your textboxes where users search that they can enter a * or % for like searches.

Just food for thought.|||I doing this search on 6 NTEXT fields...
so I thought maybe it will be faster if I do it on one field that has the content of the all 6...

what do U think ?

+
How should I write it ?
"SELECT * FROM X WHERE Key_Words LIKE %'" & SearchString & "'%" ?|||' SELECT * FROM X WHERE FirstName ' + ' LIKE ''' + '%' + @.FirstName + '%'''
.LastName ' + ' LIKE ''' + '%' + @.LastName + '%'''

ANd keep appending for each additional field until you have all six included.

If this is a stored procedure assign your string to variable and then at the end
call exec yourVarialbename

For example

EXEC (@.SQLStatement)