SQL LIKE % FOR INTEGERS

Question

In T-SQL, how do I write the query to select rows for any integer value for a column??

For example, the data is like this

NAME,AGE
A,10
B,20
C,10
D,20

and There's a <asp:dropdownlist> that has two options, 10,20, so that a user can select either 10 or 20. If the user selects 10 or 20, The data is being pulled correctly, but how do I say a * condition?? - like select ALL data for ANY value in the age column??

My code is as follows,

select ...where (AGE = @AGE)

<SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="AGE" PropertyName="SelectedValue" DbType="Int32" DefaultValue="ANY"

Also, the follow query works perfectly in the SSMS, but how to implement this behavior in asp.net SqlDataSource??

SELECT * FROM [TABLE] where AGE is not null

If the column AGE was of varchar type, I am able to use the '%', but it's an numeric field

Thanks,

Answer 1

To provide an ALL/ANY option, you need to specify a sentinel value -- a value that will never exist in your dataset -- so you can check the variable submitted to the stored procedure in order to know when to ignore the variable and use the correct WHERE clause.

IE: If the drop down list has an element with the display text of "All", and a value of -1, the following dynamic SQL would be appropriate:

DECLARE @SQL NVARCHAR(MAX)

   SET @SQL = N'SELECT * 
                  FROM [YOUR_TABLE]
                 WHERE 1 = 1 '

   SET @SQL = @SQL + CASE 
                       WHEN @age > 0 THEN 
                         ' AND age = @age '
                       ELSE 
                         ' AND age IS NOT NULL '
                     END

BEGIN

  EXEC sp_executesql @SQL N'age INT', @age

END

See this link for more details about dynamic SQL in TSQL/SQL Server.

But you don't have to use dynamic SQL - this is equivalent:

IF @age > 0 
BEGIN

   SELECT * 
     FROM [YOUR_TABLE]
    WHERE age IS NOT NULL

END
ELSE
BEGIN

   SELECT * 
     FROM [YOUR_TABLE]
    WHERE age = @age

END

...just that you can imagine how unwieldy this gets if you have multiple parameters that are independent of one another.

Answer 2

The standard answer to this using just declarative data sources is to use an OR clause in the SQL Statement

WHERE (age = @age OR @age = -1)

For certain column types, you'll want to compare to NULL, but you've already stated that -1 was your sentinel value for this column in this application. Keep an eye on performance with this, eventually you may want to switch to dynamic sql, but this technique works fine with most moderately sized data sets.

Answer 3

See if this works for you. Turn "ANY" into a NULL and use COALESCE to match against AGE.

If "param" is "ANY", then we turn it into NULL and then return "age", so that everything matches. If "param" is anything else, only rows with matching ages will be returned.

CREATE TABLE #table (
  age int
)
GO

INSERT INTO #table VALUES (10)
INSERT INTO #table VALUES (20)
INSERT INTO #table VALUES (30)
GO

DECLARE @param varchar(10)
SET @param = 10
SELECT * FROM #table WHERE age = @param

SET @param = 'ANY'
SELECT * FROM #table WHERE age = COALESCE(NULLIF(@param,'ANY'),age)

Results

age
-----------
10

(1 row(s) affected)

age
-----------
10
20
30

(3 row(s) affected)

Answer 4

Do not include it in the where

Answer 5

A typical practice is to start with a base query which returns all results:

string sql = "SELECT * FROM [TABLE] WHERE 1=1";

Then if you have an optional parameter selected, add to the string.

sql += " AND AGE = @age";

1=1 always evaluates to true, so it's there to eliminate the decision as to whether you need to put an AND or not before your optional condition.

To do this in a SqlDataSource you'll probably mix my and OMG Ponies approach:

datasource.SelectCommand = "SELECT * FROM [TABLE] WHERE 1=1 AND (AGE = @age OR @age = -1)";

Answer 6

To answer the original question, or at least to the title of the question 'SQL LIKE % FOR INTEGERS', just because there will be others coming to this page because of the title - just like me.

Given you have table 'Ages'

NAME,AGE
A,10
B,20
C,10
D,20
E,11

and you want all with age 10-19, or round ages (*0) you will need a LIKE % FOR INTEGERS here is how this can be done with a subquery:

SELECT * FROM (SELECT CAST(AGE AS VARCHAR) as Age FROM Ages) tmp where Age like '1%'

OR

SELECT * FROM (SELECT CAST(AGE AS VARCHAR) as Age FROM Ages) tmp where Age like '%0'

Have Fun

Answer 7

You should add it as an optional select clause, example...

SELECT *
FROM <Table>
WHERE (@Age is Null OR @Age = '' OR @Age = Age)

Now if you return a null or blank value for age, it will be ignored.

Answer 8

If you want one query where you can pass in one parameter and work with both conditions then you use something like

Select Name, Age from 
(
      SELECT 
       (CASE 
        WHEN @p1 = '*' THEN '*'   
        ELSE    CONVERT(NVarChar,Age)
        END) AS SearchCriteria, 
        Name, 
        Age
    FROM table
 ) 
 t
 where SearchCriteria = @p1

Parameter must be a NVarchar, but can be '*' or '10' or '20'

Warning. Performance will not be great (Ok, it will be terrible if the table has a lot of rows). It might be better if you used a value of 0 if you want all ages which would remove the need to convert everything to strings.

Answer 9

Are you trying to use one SQL query for all cases no matter what the user selects? It would be much easier to use two different queries depending on user selection. When the user selects "ANY"

SELECT * FROM Table WHERE @Age > 0 

This would match all ages.