SQL concat in Where clause

How can I accomplish this?

Select emailAddress
From UserList
Where (fname||" "||lname) =  'John Doe'
LVL 13
RickAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Check the attached script.

Raj
create table #tblUsers
(
	FName  varchar(25), 
	LName		varchar(25), 
	Email	varchar(25)
)

insert into #tblUsers
select 'John', 'Doe', 'jdoe@email.com '


select * from #tblUsers where FName +  ' ' + LName =  'John Doe' 

drop table #tblUsers

Open in new window

0
 
aelliso3Commented:
Select emailAddress
From UserList
Where (fname + ' ' + lname) =  'John Doe'
0
 
Éric MoreauSenior .Net ConsultantCommented:
aren't the + enough:

Select emailAddress
From UserList
Where (fname + " " + lname) =  'John Doe'
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
RickAuthor Commented:
aelliso3 - I get nothing
emoreau - Invalid column name ' '.
0
 
aelliso3Commented:
Try this:
 
Select emailAddress
From UserList
Where fname = 'John' and lname = 'Doe'
0
 
Éric MoreauSenior .Net ConsultantCommented:
can you provide more information on what you are doing because the error makes no sens to me !
are you running this query from SSMS?
0
 
aelliso3Commented:

emoreau ... yours had 2 double quotes instead of 2 single quotes ...
0
 
Éric MoreauSenior .Net ConsultantCommented:
doh!
0
 
RickAuthor Commented:

I have this table:  

tblItems:  
ItemID, CreatedBy, CreatedDate
001, John Doe, 9/10/2010 10:15:00 PM
 

And this table:
 tblUsers:
FName, LName, Email
John, Doe, jdoe@email.com
 
I want to select the email address from tblUsers of whom created ItemID 001. In this case John Doe's email.
 
0
 
RickAuthor Commented:
I'm using vb, I can split 'John Doe' and do what you said above:
 
Select emailAddress
From UserList
Where fname = 'John' and lname = 'Doe'
But I was hoping to get this accomplished directly from my SQL query.
0
 
aelliso3Connect With a Mentor Commented:
Select *
from tblItems
     INNER JOIN tblUsers
          ON tblItems.CreatedBy = tblUsers.FName + ' ' + tblUsers.FName

Then you can use any field to get the values you want
0
 
Rajkumar GsSoftware EngineerCommented:
Nice to help you :-)

It's my 400th accepted answer!

Raj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.