Using computed column in CharIndex
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