SQL server adding where clause that doesn't affect results

Question

I have the following query

  select count(person),  from [table]
  where (date > '2015-01-01')
  group by person

This query returns 100 as result, i need to know how to add another condition in where clause using LIKE and getting the same result (100)

  select count(person),  from [table]
  where (date > '2015-01-01' and name LIKE '%?%')
  group by person

The name column event if it's added it should have no effect on the final result

That's a strange request. Assuming name is never NULL, changing the end to `'%'` should do it. — Nick.McDermaid, Apr 21 '15 at 12:33
Be careful with such patterns for writing generalised search queries *(where one query can full-fill many different criteria)*; it can totally hammer performance... http://sommarskog.se/dyn-search.html — MatBailie, Apr 21 '15 at 13:27

score 1 · Accepted Answer · answered Apr 21 '15 at 12:51

1

Here is two way of doing this, but what is the meaning of this?

select count(person),  from [table]
where (date > '2015-01-01' and (name LIKE '%' or name is null))
group by person

Or:

select count(person),  from [table]
where (date > '2015-01-01' and (name LIKE '%sometext%' or 1 = 1))
group by person

answered Apr 21 '15 at 12:51

Giorgi Nakeuri

Thats works, well i need it in my asp.net application i'm doing some checkings so i'm using it. – drex drex Apr 21 '15 at 12:58

Tim Schmelter · Answer 2 · 2015-04-21T12:43:00.650

0

Are you aware of the TOP clause?

select TOP 100 count(person) As PersonCount, Person
from [table]
where (date > '2015-01-01' and name LIKE '%?%')
group by person

edited Apr 21 '15 at 12:43

answered Apr 21 '15 at 12:37

Tim Schmelter

I need to get first 100 results, is their any drawback using it ? – drex drex Apr 21 '15 at 12:40
No, the `?` has no special meaning in a `LIKE` as far as i know. http://stackoverflow.com/questions/712580/list-of-special-characters-for-sql-like-clause – Tim Schmelter Apr 21 '15 at 12:41
That's strange i'll try to find another solution – drex drex Apr 21 '15 at 12:46

Rahul Tripathi · Answer 3 · 2015-04-21T12:39:39.470

0

Try this:

select top 100 count(person),  from [table]
where (date > '2015-01-01' and name LIKE '%?%')
group by person

Assuming that there are more than 100 results for which name is containing ? else the result will differ.

edited Apr 21 '15 at 12:39

answered Apr 21 '15 at 12:38

Rahul Tripathi

It gives 0 record in this case it searches for values matching '?' – drex drex Apr 21 '15 at 12:39
@drexdrex:- Is your name field is containing the `?` ? – Rahul Tripathi Apr 21 '15 at 12:40
@drexdrex:- So how you are expecting the result to return a row? – Rahul Tripathi Apr 21 '15 at 12:40
1

Ooops i havent noticed it, coincidence i'm working on another query that contains TOP 100, i thought it's the one i posted. Well noticed !! – drex drex Apr 21 '15 at 12:43
@drexdrex:- No problem! happy coding! – Rahul Tripathi Apr 21 '15 at 12:44

3 Answers3