Help with concat in query

How to get this working?

SELECT Concat(city, ' ', location开发者_如何学C, ' ', location2) as src1 
FROM locations
WHERE src1 LIKE '%var%'

---

SELECT Concat( city, ' ', locatio`n, ' ', location2 ) as src1 FROM locations WHERE Concat( city, ' ', location, ' ', location2 ) LIKE '%var%'` 

will work

---

How about using the + cocantenation operator

SELECT  city + ' ' + location + ' ' + location2  as src1 
FROM    locations 
WHERE   city + ' ' + location + ' ' + location2 LIKE '%var%'

Have a look at + (String Concatenation) (Transact-SQL)

Or you could use a sub select

SELECT  *
FROM    (
            SELECT  city + ' ' + location + ' ' + location2  as src1 
            FROM    locations 
        ) vals
WHERE   vals.src1 LIKE '%var%'

Or using Sql Server 2005+ you could use a WITH statement

;WITH vals AS(
        SELECT  city + ' ' + location + ' ' + location2  as src1 
        FROM    locations
)
SELECT  *
FROM    vals
WHERE   src1 LIKE '%var%'