Use Sql server column alias in where condition

Question

I am writing a sql server query with one column as a expression like

Select name , name + age as Test from myTable
where Test like '%b%';

Now i cannot use Test either i need to write

Select name , name + age as Test from myTable
where (name + age) like '%b%';

Now the expression (name + age) can be very big sometimes so cant i give it some alias and use it

Any help is appreciated,

Thank You

May I know what is reason you want to do that way? You want to find any record that match your filter criteria in either name or age column? — Thit Lwin Oo
– Thit Lwin Oo, Commented Feb 3, 2012 at 6:55
@ThitLwinOo because sometimes my expression is too big so i want to use some alist, so i put the question here so that i may know whether this is possible or not — Moons
– Moons, Commented Feb 3, 2012 at 6:58
Ok, I provided one answer, please try. Let me know it is ok for what you want. — Thit Lwin Oo
– Thit Lwin Oo, Commented Feb 3, 2012 at 7:01

Moons · Accepted Answer · 2012-02-03 09:59:09Z

6

How about this way..

select name, Test
(
Select name , (name + age) as Test from myTable
) t
where Test like '%b%'

The query needs to be modified as it has syntax error:

So it should be written like

select name from
(
Select name , (name + age) as Test from myTable
) t
where Test like '%b%'

edited Feb 3, 2012 at 9:59

Moons

3,8644 gold badges52 silver badges83 bronze badges

answered Feb 3, 2012 at 7:00

Thit Lwin Oo

3,4283 gold badges22 silver badges23 bronze badges

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

Comments

Mikael Eriksson · Accepted Answer · 2012-02-03 07:03:22Z

2

You can use cross apply like this

select M.name , T.Test
from myTable as M
  cross apply(select name + age) as T(Test)
where T.Test like '%b%';

answered Feb 3, 2012 at 7:03

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

5 Comments

Amar Palsapure Over a year ago

+1 for Cross Apply. But it is little costlier affair if compared to Thit' answer.

Mikael Eriksson Over a year ago

@AmarPalsapure - The tests I have made does not show any difference in query plans or execution times.

Amar Palsapure Over a year ago

In the execution plan, Compute Scalar step is added, it addes to CPU cost. It's not very significant though, may be because my DB size is small.

Mikael Eriksson Over a year ago

@AmarPalsapure - There is a Compute Scalar step in the derived table query as well. (SQL Server 2008)

Amar Palsapure Over a year ago

Cross checked, the query from Thit was giving different output. Now checked everything, looks same. No difference at all. My bad. Apologies.

Collectives™ on Stack Overflow

Use Sql server column alias in where condition

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related