Retrieving all rows from a table where a column only contains 0

This is the simplest option:

select * from tbl where replace(col,'0','') = ''

      

        
        
        
      

    

If you don't make a computed column for this expression, you can choose a function-based index (note: Oracle and Postgres already support this: Sql Server since 2008, not yet) to do this:

create index ix_tbl on tbl(replace(col,'0',''))

      

        
        
        
      

    

[EDIT]

I'm just leaving the answer below for posterity, I tried to explain how to make a query usage index from a computed column.

Use this:

select * from tbl
where ISNUMERIC(col) = 1 and cast(col as int) = 0

      

        
        
        
      

    

For ISNUMERIC needs in Oracle use this: http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html

[EDIT]

@Charles, re: computed column in Oracle:

For RDBMSes supporting computed column but it doesn't have a persistent option, yes this will call a function for each row. If it supports a constant column, it will not make a function call, you have a real column in the table that is precomputed from this function. Now, if the data can cause the function to throw an exception, there are two scenarios.

First, if you didn't specify persist, this will allow you to keep the computed column ( ALTER TABLE tbl ADD numeric_equivalent AS cast(col as int)

) even if the result from the data throws an exception, but you cannot unconditionally select that column, this will raise the exception:

select * from tbl

      

        
        
        
      

    

this will not throw an exception:

select * from tbl where is_col_numeric = 1

      

        
        
        
      

    

This will:

select * from tbl where numeric_equivalent = 0 and is_col_numeric = 1

      

        
        
        
      

    

it won't (Sql server supports short-circuiting ):

select * from tbl where is_col_numeric = 1 and numeric_equivalent = 0 

      

        
        
        
      

    

For reference, the is_col_numeric above was created using this:

ALTER TABLE tbl ADD 
is_col_numeric  AS isnumeric(col)       

      

        
        
        
      

    

And this is the is_col_numeric index:

create index ix_is_col_numeric on tbl(is_col_numeric)

      

        
        
        
      

    

Now, for the second scenario, you will place a computed column with the PERSISTED option in a table that already has existing data (like "ABXY", "X1", "ETC") that throws an exception when a function / expression (like cast) is applied to to it your RDBMS won't let you make a computed column. If there is no data in your table, this will allow you to set the PERSISTED option, but after you try to insert data (for example insert into tbl(col) values('ABXY')

), which throws an exception, your RDBMS will not allow you to save your data. This way, only numeric text can be stored in your table, your computed PERSISTED column is degenerated into a constraint check, albeit completely deferred.

For reference, here's the saved computed column sample:

ALTER TABLE tbl ADD
numeric_equivalent  AS cast(col as int) persisted

      

        
        
        
      

    

Now, some of us might be tempted not to put the PERSISTED option on the computed column. It will be a kind of selfless work in terms of performance, because you may not be able to create the index afterwards. When you later want to create an index on an unmanaged computed column and the table already contains "ABXY" data, the database will not allow you to create the index. To create an index, you need to get a value from a column, and if that column throws an exception, it won't let you create an index on it.

If we try to trick the ie a bit, we will immediately create an index on that unspecified computed column when the table is created, the database will let you do that. But when we insert "ABXY" into the table later, it will not be saved, the database will automatically create indexes (indexes) after we insert data into the table. The index constructor gets an exception instead of the data, so it cannot make an index entry for the data we tried to insert, subsequently no data insertion will happen.

So how can we achieve the nirvana index on a computed column? First of all, we make sure that the calculated column is PERSISTED, and this will ensure that errors are thrown immediately; if we don't put the PERSISTED option, anything that might throw an exception will be deferred to building the index, just making things fail later. Errors are easier to find when they happen earlier. After the column is saved, put an index on it

So if we have existing data '00', '01', '2', this will allow us to make a persistent computed column. Now after that, if we insert "ABXY", it will not be inserted, the database will not be able to save anything from the computed column that threw the exception. This way, we'll just hide our own listing that doesn't throw an exception.

To use (just translate this to the Oracle equivalent):

create function cast_as_int(@n varchar(20)) returns int with schemabinding
begin

    begin try
       return cast(@n as int);
    end try
    begin catch 
       return null;
    end catch

end;

      

        
        
        
      

    

Note that catching exception in UDF does not work on Sql server yet, but Microsoft plans to support this

Now this is our non-assertive computed column:

ALTER TABLE tbl ADD
numeric_equivalent  AS cast_as_int(a) persisted

      

        
        
        
      

    

Drop the existing index, then re-create it:

create index ix_num_equiv on tbl(numeric_equivalent)    

      

        
        
        
      

    

Now this query will become indexing - citizen, performer and will not raise an exception, even the order of conditions will be canceled:

select * from tbl where numeric_equivalent = 0 and is_col_numeric = 1

      

        
        
        
      

    

To make it more perfect, since a numeric_equivalent column doesn't throw any additional exceptions, we no longer use is_col_numeric, so just use this:

select * from tbl where numeric_equivalent = 0