By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

condition in CONCAT

100+
P: 160
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
Expand|Select|Wrap|Line Numbers
  1. mysql_query("SELECT * FROM abc where CONCAT(fname,' ',mname,' ',lname) like'" . $q .  "%' order by id");
  2.  
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

Expand|Select|Wrap|Line Numbers
  1. mysql_query("SELECT * FROM abc where CONCAT(fname,' ',mname,'',lname) like'" . $q . "%' order by id");
  2.  
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
Jan 23 '09 #1
Share this Question
Share on Google+
4 Replies


mwasif
Expert 100+
P: 801
Yes, you can use IF() within CONCAT().
Jan 23 '09 #2

100+
P: 160
then please guide me about syntax
Jan 23 '09 #3

mwasif
Expert 100+
P: 801
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT CONCAT(fname,' ', IF(mname<>'', CONCAT(mname,' '), '') ,lname) FROM abc
  3.  
Jan 23 '09 #4

100+
P: 160
thanks a lot........
Jan 23 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.