condition in CONCAT

[waqasahmed996]

hi to all

i have three fields of name in database named as fnam,mname,lname. fname and lname is mandatory field and mname is optional.

i want to make a search query on name

mysql_query("SELECT * FROM abc where CONCAT(fname,' ',mname,' ',lname) like'" . $q .  "%' order by id");
 

in records where mname is not empty then above code is working properly but records in which mname is empty then i have to enter double space between fname and lname which disturb search

on the other case when i write

mysql_query("SELECT * FROM abc where CONCAT(fname,' ',mname,'',lname) like'" . $q . "%' order by id");
 

in records where mname is empty then above code is working properly but records in which mname is not empty then i have to remove space between fname and lname which disturb search

please provide me some solution. can i use if condition in concat?

i need to know if i am not clear in my question

[mwasif]

Yes, you can use IF() within CONCAT().

[waqasahmed996]

then please guide me about syntax

[mwasif]

 
SELECT CONCAT(fname,' ', IF(mname<>'', CONCAT(mname,' '), '') ,lname) FROM abc
 

[waqasahmed996]

thanks a lot........