For example, my query is something like:

SELECT fullName = firstName + ' ' + middleName + ' ' + lastName,
streetAddress = address1
FROM Person

How would I be able to reference fullName directly without having to type out first+middle+last again? If I just added "WHERE fullName = 'John X Doe'", i get the error that fullName is an invalid column.

I've tried throwing it into a variable

SELECT @fullNameVar = firstName + ' ' + middleName + ' ' + lastName,
fullName = @fullNameVar
streetAddress = address1
FROM Person
WHERE @fullNameVar = 'John X Doe'

but I cannot use a select statement to set a variable when I am also retrieving other data.

SELECT * FROM (
  SELECT firstName + ' ' + middleName + ' ' + lastName AS fullName, address1 AS streetAddress
  FROM Person
) P
WHERE fullName = 'John X Doe'
SELECT * FROM (
  SELECT firstName + ' ' + middleName + ' ' + lastName AS fullName, address1 AS streetAddress
  FROM Person
) P
WHERE fullName = 'John X Doe'

Exactly what I was looking for. Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.