SQL query, can i have WHERE as a wildcard?

Question

Hello everybody!

Is it possible to have the WHERE as a wildcard in a SQL query?

I have this simple query:

SQL = "SELECT ID, Firstname, Lastname, Company, City, Email FROM table WHERE * LIKE '%" & q & "%';"

lets say the variable q equals "John Doe". Then I want the query to search for q in both Firstname AND/OR (??) Lastname.

I just can figure it out.

Thanks alot /Andreas

EDIT/UPDATE:

I will try to simplify my question.

Lets say I have a db-table with the ID, Firstname, Lastname and Email fields. (fx with the values: 5, john, Doe, [email protected])

I then have a simple html form with a searchfield (one simple text input with a submit button) on a page where a visitor can search for any of the fields above.

if he/she searches for the word "John", or "Doe", or "hotmail" it works perfect with the SQL query that gets the searchstring as the variable "q" on my ASP page like this:

SQL = "SELECT ID, Firstname, Lastname,Email FROM table WHERE ID LIKE '%" & q & "%', OR Firstnamne LIKE '%" & q & "%', OR Lastname LIKE '%" & q & "%' OR Email LIKE '%" & q & "%';"

Now my question is: How do I do if the visitor types in "John Doe"?

I want to be able to get a list of all the people in my db named "John Doe"

Answer 1

SELECT  ID, Firstname, Lastname, Company, City, Email
FROM    mytable
WHERE   firstname LIKE '%" & q & "%' OR lastname LIKE '%" & q & "%' -- Replace OR with AND as necessary

Answer 2

No you must specify the fields you want to search. Of course you realize that using wildcards at the beginning of a like statement will also mean that indexes can't be used and your query will be slow. Do you really need those wildcards at the beginning? From what you are asking I suspect not.

If your variable includes both the first and last name this query might also work:

SELECT  ID, Firstname, Lastname, Company, City, Email
FROM    mytable
WHERE   firstname + " " +lastname LIKE '%" & q & "%'

Of course if either first or lastname can contain nulls you might need to add an isnull or coalesce to that.

Answer 3

I have done this approach in the past using stored procedures:

SELECT  ID, Firstname, Lastname, Company, City, Email
FROM    mytable
WHERE
    (@pFirstName is null or FirstName = @pFirstName)
    and (@pLastName is null or LastName = @pLastName)
    and (@pCompany is null or Company = @pCompany)
    and (@pCity is null or City = @pCity)
    and (@pEmail is null or Email = @pEmail)

You would define a parameter for each comparison that you might want to include in your where clause; if you don't want to query by a particular field then you just pass null to the corresponding parameter, or don't pass a value to it at all and let the default value of the parameter be null.

Edit: If you want to switch between "OR" and "AND", then you could add another set of parameters. I know it seems cluttered with so many parameters, but it works:

SELECT  ID, Firstname, Lastname, Company, City, Email
FROM    mytable
WHERE
    (@pAndFirstName is null or FirstName = @pAndFirstName)
    and (@pAndLastName is null or LastName = @pAndLastName)
    and (@pAndCompany is null or Company = @pAndCompany)
    and (@pAndCity is null or City = @pAndCity)
    and (@pAndEmail is null or Email = @pAndEmail)
    and ( (
     -- either all of the "OR" parameters are null
     @pOrFirstName is null
     and @pOrLastName is null
     and @pOrCompany is null
     and @pOrCity is null
     and @pOrEmail is null
    ) or (
    -- or at least one of them must match
    or (FirstName = @pOrFirstName)
    or (LastName = @pOrLastName)
    or (Company = @pOrCompany)
    or (City = @pOrCity)
    or (Email = @pOrEmail)
    ))

Of course, you can choose to use the LIKE operator instead of = if you need to.

Answer 4

Obligatory XKCD

Answer 5

Concatenate all fields, then use a single "LIKE":

 SELECT ID, Firstname, Lastname, Company, City, Email 
   FROM table 
   WHERE ID||Firstname||Lastname||Company||City||Email  LIKE '%" & q & "%';"

Answer 6

I would suggest that you look into full text searching. It gives several benefits and allows you to search across several columns.

Depending on which SQL Enginge you use, it will be more or less powerful.

I dont have the syntax fresh in mind, but for MySQL it's something like:

When creating table:

CREATE TABLE ... (
      ID ...
,     ...
,     FULLTEXT (Firstname, Lastname, Company, City, Email)
);

Then do:

SELECT * 
FROM ...
WHERE MATCH (Firstname, Lastname, Company, City, Email) AGAINST ('<KEYWORD>') > 0;

Check the manual of your SQL engine for proper syntax/useage.

Also: As Lucero is hinting: Remember about SQL injection. Everyone that replied and did not mention this should be downvoted ;-)

Answer 7

In order to do what you're looking for, I think you should use dynamic SQL. Basically concatenate the elements of your query into an nvarchar(max) variable, and the run the query by using the EXEC or sp_execsql commands. Note that the sp_execsql supports parameterized queries, whereas EXEC does not.

Hope that helps!

Answer 8

OR'ing a bunch of condition in SQL's WHERE or ON clause is terrible for performance.

If you do not like my suggestion about FULL TEXT indices, I'd suggest another option here. It's also not optimal for performance, becuase of wildcard in beginning of LIKE, but possibly better than many OR's.

Create a new column on your table, which is a concatenation of the fields you wish to search. This can be a computed column if you SQL engine supports it.

In MS SQL, it would look something like:

ALTER TABLE ... ADD COLUMN MatchCode AS FirstName + LastName + Email + ...

If you cannot use computed columns, you will have to set the value of this column on each update:

UPDATE x
SET  MatchCode = FirstName + LastName + <NewEmailValue> + ...
,    Email = <NewEmailValue>
FROM ... AS x
WHERE ...

Then you can simple do

SELECT ...
FROM ... AS x
WHERE x.MatchCode LIKE '%<keyword>%'

Again, remember about SQL injection :)