asked on

Concat in Select for Search Return

I am working on an auto suggest and I would like people to be able to either type in a product number or product name. So for example

If they start to type 1234 it will start to show the products

12345: The Product 1
12346: The Product 2

Or if they type in Product
It would show the same results

So how can i can I concatenate the product id and product name in a query and have it format like that, even with the colon in it?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

theideabulb

ASKER

if i am doing an autosuggest, would i still need to do the two LIKE statements, if you are combining them and use an alias

select concat(product_id, ': ' , product_name as theProduct

could you just search it as one string?

Guy Hengel [angelIII / a3]

you could. ... but it would be as inefficient as doing the "or" as I showed (because of the LIKE '% ... %' part) ...

theideabulb

ASKER

so this wouldn't be a good way to do it?

SELECT set_id, CONCAT(product_number,': ',product_name) as theProduct FROM products
having lower(theProduct) like '%1234%'

theideabulb

ASKER

Thank you for your help.

Guy Hengel [angelIII / a3]

from what I know, you could not even do that in terms of syntax (you cannot use the alias except in the ORDER BY)
also, using HAVING only makes sense in GROUP BY queries ...