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?

caramba
  • 21,963
  • 19
  • 86
  • 127

3 Answers3

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%";
BenM
  • 52,573
  • 26
  • 113
  • 168
  • 2
    Pay attention: `HAVING` will be executed after the query. MySQL first execute the query and than apply having clause to the result set. – gieffe Nov 22 '13 at 10:06
  • In my opinion "where concat" is better. – gieffe Nov 22 '13 at 10:09
  • 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. – BenM Nov 22 '13 at 10:10
  • the select gets called by ajax on keyup + a few ms. I try to make a dynamic search – caramba Nov 22 '13 at 10:11
  • I'd recommend `HAVING` then. – BenM Nov 22 '13 at 10:12
4

You can't use aliases in where clauses.

SELECT CONCAT(names,' ',office) AS bigDataField
FROM item_table 
WHERE CONCAT(names,' ',office) LIKE "%jessy%";
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • thank you guys so much for your resposnes! is it better to use HAVING like BenM writes? If I look here http://sqlfiddle.com/#!2/21b5c/10 one time your example is faster and another time benms example – caramba Nov 22 '13 at 10:01
  • `having` clauses are for grouped data. For the sake of readability I would differ to use them. – juergen d Nov 22 '13 at 10:03
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?

Community
  • 1
  • 1
Steph Locke
  • 5,951
  • 4
  • 39
  • 77