How do you query an int column for any value?

问题:

How can you query a column for any value in that column? (ie. How do I build a dynamic where clause that can either filter the value, or not.)

I want to be able to query for either a specific value, or not. For instance, I might want the value to be 1, but I might want it to be any number.

Is there a way to use a wild card (like "*"), to match any value, so that it can be dynamically inserted where I want no filter?

For instance:

select int_col from table where int_col = 1  // Query for a specific value
select int_col from table where int_col = *  // Query for any value

The reason why I do not want to use 2 separate SQL statements is because I am using this as a SQL Data Source, which can only have 1 select statement.

回答1:

Sometimes I would query for actual value (like 1, 2...) so I can't not have a condition either.

I take it you want some dynamic behavior on your WHERE clause, without having to dynamically build your WHERE clause.

With a single parameter, you can use ISNULL (or COALESCE) like this:

 SELECT * FROM Table WHERE ID = ISNULL(@id, ID)

which allows a NULL parameter to match all. Some prefer the longer but more explicit:

 SELECT * FROM Table WHERE (@id IS NULL) OR (ID = @id)

回答2:

A simple answer would be use: IS NOT NULL. But if you are asking for say 123* for numbers like 123456 or 1234 or 1237 then the you could convert it to a varchar and then test against using standard wild cards.

In your where clause: cast(myIntColumn as varchar(15)) like '123%'.

回答3:

Assuming the value you're filtering on is a parameter in a stored procedure, or contained in a variable called @Value, you can do it like this:

select * from table where @Value is null or intCol = @Value

If @Value is null then the or part of the clause is ignored, so the query won't filter on intCol.

回答4:

The equivalent of wildcards for numbers are the comparators.

So, if you wanted to find all positive integers:

select int_col from table where int_col > 0

any numbers between a hundred and a thousand:

select int_col from table where int_col BETWEEN 100 AND 1000

and so on.

回答5:

I don't quite understand what you're asking. I think you should use two different queries for the different situations you have.

When you're not looking for a specific value:

SELECT * FROM table

When you are looking for a specific value:

SELECT * FROM table WHERE intcol = 1 

回答6:

You can use the parameter as a wildcard by assigning special meaning to NULL:

DECLARE @q INT = 1
SELECT * FROM table WHERE IntegerColumn = @q OR @q IS NULL

This way, when you pass in NULL; you get all rows.

If NULL is a valid value to query for, then you need to use two parameters.

回答7:

If you really want the value of your column for all rows on the table you can simply use

select int_col
  from table 

If you want to know all the distinct values, but don't care how many times they're repeated you can use

select distinct int_col
  from table

And if you want to know all the distinct values and how many times they each appear, use

select int_col, count(*)
  from table
  group by int_col

To have the values sorted properly you can add

  order by int_col

to all the queries above.

Share and enjoy.