CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Andrei Botchin
Andrei Botchin

Reputation: 37

Error with creating sql data name

I want to make search by name and surname , and I use this Query(SUR - surname , NAM - name 

Declare @name varchar(30) = 'Joh'  //part or full name/surname or both
SELECT (RTRIM(SUR) + ' ' + RTRIM(NAM)) AS name , W , SS FROM Table
WHERE name like '%' + @name + '%'

But I have an error

Invalid column name "name".

I need to make search by 2 columns in same time . This 

(SUR like '%' + @name + '%') or (NAM like '%' + @name + '%')

gave me search only by 1 column like : Search : Jones and I see Johnes but if i want search John Jones i will haven't result .

Help me to make search by 2 columns in same time.

Upvotes: 1

Views: 82

Answers (2)

Mark Sinkinson
Mark Sinkinson

Reputation: 976

You can use APPLY VALUES to achieve this as well.

It's not the best SQL design to begin with, as you won't be able to take advantage of indexing.

Declare @name varchar(30) = 'Joh';  //part or full name/surname or both

SELECT 
  Fullname.name AS name , 
  W , 
  SS 
FROM Table
OUTER APPLY(VALUES(RTRIM(SUR) + ' ' + RTRIM(NAM)) Fullname(name)
WHERE Fullname.name like '%' + @name + '%';

Upvotes: 0

Brave Soul
Brave Soul

Reputation: 3620

you are not alowed to use alias name in where clause. you have to use your expression as it is in where clause

Declare @name varchar(30) = 'Surname'  
SELECT (RTRIM(SUR) + ' ' + RTRIM(NAM)) AS name , W , SS FROM Table
WHERE (RTRIM(SUR) + ' ' + RTRIM(NAM)) like '%' + @name + '%'

Upvotes: 4

Related Questions