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)
