function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
asadimasadim 

SOQL inline query: how to concat fields?

Hi,

 

Inside Contact I'd like to look for all contacts whose first name + ' ' + last name equals a local varialbe, say myVar. I can't figure out how to do the concatenation. I've tried :(FirstName + ' ' + LastName) and that didn't work. This is an inline query. Any ideas please? Thanks!

Best Answer chosen by Admin (Salesforce Developers) 
SelectedPartnerSelectedPartner
You can't do any caculation in SOQl as you do in SQL. So you can't put this expression FirstName + ' ' + LastName in a SOQL query string. 

Anyway, if you wanna get the full name, why don't you just use the Name filed which actually is a concat of FirstName and Lastname. 

All Answers

Venkat PolisettVenkat Polisett
asadim wrote:

Hi,

 

Inside Contact I'd like to look for all contacts whose first name + ' ' + last name equals a local varialbe, say myVar. I can't figure out how to do the concatenation. I've tried :(FirstName + ' ' + LastName) and that didn't work. This is an inline query. Any ideas please? Thanks!

I assume you are trying this in Apex. Why don't you use dynamic APEX:

 

String firstName = 'Put First name here';
String lastName = 'Put Last name here';

// I am just makeing up a variable here
String fl = firstName + ' ' + lastName;

String str = 'SELECT name, id, (SELECT name, id FROM Contacts where name =  \'' + fl + '\') FROM Account LIMIT 10';

System.debug(str);

Account[] alist = (Account[]) Database.Query(str);

for (Account a : alist)
{
	System.debug('Account = ' + a.name);

	if (a.Contacts.size() > 0)
	{
		for (Contact c : a.Contacts)
		{
			System.debug('Contact = ' + c.name);
		}
	}
}


 


asadimasadim
Thanks. I'm doing my query over a bunch of lists like so (the part in red is where I'm stuck at):
 
 
Contact[] contactMatch= [
     	SELECT Id, LastName, MailingStreet, AccountId 
     	FROM Contact 
     	WHERE ((LastName in :acctsNames.values() or :(FirstName + ' ' + LastName) in :acctsNames.values()) and mailingStreet in :acctsAddress.values() and (AccountId in :acctsChanged.keySet() or AccountId = null)) limit 2];
 
 As such, if I were to do it using dynamic SOQL then I assume I had to get everything from those lists and put them in a string, and then append that string to the query? If so then I find that to be an unnecessarily lengthy way -and I was hoping if there was a more reasonable way to do this. 
 
 
Message Edited by asadim on  09-09-2009 05:11 PM


IanRIanR
Unfortunately, you can't do this in a SOQL query :(
 
You could create a custom field that concatenates the two fields, and use this in your query?


SelectedPartnerSelectedPartner
You can't do any caculation in SOQl as you do in SQL. So you can't put this expression FirstName + ' ' + LastName in a SOQL query string. 

Anyway, if you wanna get the full name, why don't you just use the Name filed which actually is a concat of FirstName and Lastname. 

This was selected as the best answer
asadimasadim
Great. The Name field seems to have worked! Thanks to everyone who replied. They were all good answers but this board lets me choose only 1, so there you go :)