Searching through multiple fields
-
I'm wondering if someone could help me with the SQL I'd need to accomplish the following: I have a db with a number of text fields and what I want to do is have a search box in my program that will search all fields for the text entered. For example I have first_name, last_name, alias, etc. and let's say I enter "Jim" in the search field. I want to be able to search all the fields (first_name, last_name, etc) for that term. Is this possible? I'd also like to use the LIKE operator so the text doesn't have to be exact. Thanks!
- Aaron
-
I'm wondering if someone could help me with the SQL I'd need to accomplish the following: I have a db with a number of text fields and what I want to do is have a search box in my program that will search all fields for the text entered. For example I have first_name, last_name, alias, etc. and let's say I enter "Jim" in the search field. I want to be able to search all the fields (first_name, last_name, etc) for that term. Is this possible? I'd also like to use the LIKE operator so the text doesn't have to be exact. Thanks!
- Aaron
Maybe something like this:
Select * from table where first_name like '%searchString%' or last_name like '%searchString%' or ....OrSelect * from table where first_name + last_name + .... like '%searchString%'-- Don't take life seriously because you can't come out of it alive. -Warren Miller
-
Maybe something like this:
Select * from table where first_name like '%searchString%' or last_name like '%searchString%' or ....OrSelect * from table where first_name + last_name + .... like '%searchString%'-- Don't take life seriously because you can't come out of it alive. -Warren Miller
That seems to work quite well. Wondering if you could help with another question...how would I make the search string dynamic, as in I want to be able to pass in the value as a paramter...can I do this, if so what's the syntax? Thanks very much for the help.
- Aaron
-
That seems to work quite well. Wondering if you could help with another question...how would I make the search string dynamic, as in I want to be able to pass in the value as a paramter...can I do this, if so what's the syntax? Thanks very much for the help.
- Aaron
I did not get it. Could you explain a little more about what you want to do?
-- Don't take life seriously because you can't come out of it alive. -Warren Miller
-
I did not get it. Could you explain a little more about what you want to do?
-- Don't take life seriously because you can't come out of it alive. -Warren Miller
Sure. This is basically what my T-SQL looks like:
SELECT * FROM dbo.Books
WHERE title LIKE '%search%' OR author LIKE '%search%'What I want to do is be able to pass in "search" as a parameter. I'm not sure how to do this though because if I remove the quotes around the search sting and add the @ in front of it, I get a syntax error. I hope that's clearer. Thanks again for the help.
- Aaron
-
Sure. This is basically what my T-SQL looks like:
SELECT * FROM dbo.Books
WHERE title LIKE '%search%' OR author LIKE '%search%'What I want to do is be able to pass in "search" as a parameter. I'm not sure how to do this though because if I remove the quotes around the search sting and add the @ in front of it, I get a syntax error. I hope that's clearer. Thanks again for the help.
- Aaron
Maybe something like this:
Declare @a varchar(50) SELECT * FROM dbo.Books WHERE title LIKE '%' + @a + '%' OR author LIKE '%' + @a + '%'-- Don't take life seriously because you can't come out of it alive. -Warren Miller