Error with creating sql data name

Question

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.

You can't reference an alias like that - you'll need to either use a sub-select or replace your WHERE name LIKE with WHERE (RTRIM(SUR) + ' ' + RTRIM(NAM)) LIKE — Siyual
– Siyual, Commented Jun 26, 2015 at 13:56
@Siyual yea. thanks . It's so simple and good solution , that i did't think about it — Andrei Botchin
– Andrei Botchin, Commented Jun 26, 2015 at 13:59

Brave Soul · Accepted Answer · 2015-06-26 13:57:09Z

4

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 + '%'

answered Jun 26, 2015 at 13:57

Brave Soul

3,6504 gold badges33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Sinkinson · Accepted Answer · 2015-06-26 15:15:57Z

0

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 + '%';

answered Jun 26, 2015 at 15:15

Mark Sinkinson

9767 silver badges18 bronze badges

Collectives™ on Stack Overflow

Error with creating sql data name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related