Page 1 of 1

joining two columns in a select statement

Posted: Tue Dec 07, 2010 10:55 am
by Wilbo
Hi there,

I'm trying to make a simple search engine to search a list of names in a database.
The names are stored in two columns, first_name and last_name.
I plan on using a simple LIKE statement using the exploded results from the search box to create the query.

But because the first names and last names are stored in separate columns I want to concatinate the two columns BEFORE I apply the LIKE condition so its as if I'm searching for results from a single column.

I thought of using:

Code: Select all

SELECT CONCAT(first_name, last_name) AS name FROM users WHERE name LIKE '%john%' OR name LIKE '%smith%'
but this doesn't work.

Can anyone tell me how to do this?

Re: joining two columns in a select statement

Posted: Tue Dec 07, 2010 10:58 am
by AbraCadaver
Not tested, but I think you have to cancat them again in the where clause:

[text]SELECT CONCAT(first_name, last_name) AS name FROM users
WHERE CONCAT(first_name, last_name) LIKE '%john%' OR CONCAT(first_name, last_name) LIKE '%smith%'[/text]

Re: joining two columns in a select statement

Posted: Wed Dec 08, 2010 4:51 am
by Wilbo
Thanks, that worked a treat!