3

I'm trying to create little search engine for only a small database. "CONCAT () AS name" does not work so how can I use something like Concat() as xxx ?

Created an example here http://sqlfiddle.com/#!2/21b5c/5

What I try to do is 

SELECT CONCAT(names,' ',office) AS bigDataField FROM item_table WHERE bigDataField LIKE "%jessy%";

What is the best way to use the concat field?

Improve this question
0

3 Answers 3

Reset to default
5

You could use the HAVING clause, rather than duplicating the CONCAT() function in your WHERE clause.

For example:

SELECT CONCAT(`names`,' ',`office`) `bigDataField` FROM `item_table` HAVING `bigDataField` LIKE "%jessy%";
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Pay attention: HAVING will be executed after the query. MySQL first execute the query and than apply having clause to the result set.
In my opinion "where concat" is better.
It depends upon your data set, and how many results you expect. I've never been a big fan of calling the same function twice. You wouldn't run a Haversine formula in the select AND where clauses.
the select gets called by ajax on keyup + a few ms. I try to make a dynamic search
I'd recommend HAVING then.
4

You can't use aliases in where clauses.

SELECT CONCAT(names,' ',office) AS bigDataField FROM item_table WHERE CONCAT(names,' ',office) LIKE "%jessy%";
Improve this answer

2 Comments

thank you guys so much for your resposnes! is it better to use HAVING like BenM writes? If I look here sqlfiddle.com/#!2/21b5c/10 one time your example is faster and another time benms example
having clauses are for grouped data. For the sake of readability I would differ to use them.
1

Unfortunately, you can't use an alias for a calculated column in a WHERE clause - you will need to use CONCAT(names,' ',office) instead in your WHERE clause.

This post has more detail: Can you use an alias in the WHERE clause in mysql?

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.