Using computed column in CharIndex I am trying to first Concat two columns and then perform a search operation using CHARINDEX on that calculated column as below :

 SELECT CONCAT(FirstName, ' ', LastName) as FullName FROM Customer WHERE CHARINDEX('Martin', FullName, 1) &lt;&gt; 0


however, I am unable to use the calculated column (FullName) inside the CHARINDEX function.

The error I am getting is :


  Invalid column name 'FullName'.


What mistake am I committing here?
In addition to Scott's answer, you can just move the concat to the where clause as well. 

SELECT CONCAT(FirstName, ' ', LastName) as FullName 
FROM Customer 
WHERE CHARINDEX('Martin', CONCAT(FirstName, ' ', LastName), 1) &lt;&gt; 0


Or, as a derived table.

SELECT FullName 
FROM(
SELECT CONCAT(FirstName, ' ', LastName) as FullName 
FROM Customer) x
WHERE CHARINDEX('Martin', FullName, 1) &lt;&gt; 0


As for why you can't use the alias in the WHERE clause, it's because queries don't start with SELECT. Your WHERE is evaluated before the column list is selected.


Julia Evans: SQL Queries don't start with select
Similar Question on Stackoverflow