Getting all rows from a Table where the column contains only 0

I got a little problem

i need a sql query that gives all rows back that only co开发者_Go百科ntains 0 in it.

the column is defined as varchar2(6) the values in the column looks like this:

Row      Value
1        0
2        00
3        00
4        100
5        bc00
6        000000
7        00000

my first solution would be like this:

Oracle:
substr('000000' || COLUMN_NAME, -6) = '000000'

SQL Server:
right('000000' + COLUMN_NAME, 6) = '000000'

is there an other way? (it needs to work on both systems)

the output would be the row 1,2,3,6,7

---

This is the simplest one:

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

If you will not make computed column for that expression, you can opt for function-based index(note: Oracle and Postgres already supports this; Sql Server as of version 2008, not yet) to make that performant:

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

[EDIT]

I just keep the answer below for posterity, I tried to explain how to make the query use index from computed column.

Use this:

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

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

[EDIT]

@Charles, re: computed column on Oracle:

For RDBMSes that supports computed column but it doesn't have persisted option, yes it will make function call for every row. If it supports persisted column, it won't make function call, you have real column on the table which is precomputed from that function. Now, if the data could make the function raise an exception, there are two scenarios.

First, if you didn't specify persist, it will allow you to save the computed column (ALTER TABLE tbl ADD numeric_equivalent AS cast(col as int)) even if the result from the data will raise an exception, but you cannot unconditionally select that column, this will raise exception:

select * from tbl

this won't raise exception:

select * from tbl where is_col_numeric = 1

this will:

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

this 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 is_col_numeric's index:

create index ix_is_col_numeric on tbl(is_col_numeric)

---

Now for the second scenario, you put computed column with PERSISTED option on table that already has existing data(e.g. 'ABXY','X1','ETC') that raises exception when function/expression(e.g. cast) is applied to it, your RDBMS will not allow you to make a computed column. If your table has no data, it will allow you to put PERSISTED option, but afterwards when you attempt to insert data(e.g. insert into tbl(col) values('ABXY')) that raises an exception, your RDBMS will not allow you to save your data. Thereby only numeric text can be saved in your table, your PERSISTED computed column degenerate into a constraint check, albeit a full detoured one.

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

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

Now, some of us might be tempted to not put PERSISTED option on computed column. This would be kind of self-defeating endeavor in terms of performance purposes, because you might not be able to create index on them later. When later you want to create index on the unpersisted computed column, and the table already has data 'ABXY', the database won't allow you to create an index. Index creation need to obtain the value from column, and if that column raises an exception, it won't allow you to create index on it.

If we attempt to cheat a bit i.e. we immediately create an index on that unpersisted computed column upon table creation, the database will allow you to do that. But when we insert 'ABXY' to table later, it will not be saved, the database is automatically constructing index(es) after we insert data to the table. The index constructor receives exception instead of data, so it cannot make an index entry for the data we tried inserting, subsequently inserting data will not happen.

So how can we attain index nirvana on computed column? First of all, we make sure that the computed column is PERSISTED, doing this will ensure that errors kicks-in immediately; if we don't put PERSISTED option, anything that could raise exception will be deferred to index construction, just making things fail later. Bugs are easier to find when they happen sooner. After making the column persisted, put an index on it

So if we have existing data '00','01', '2', this will allow us to make persisted computed column. Now after that, if we insert 'ABXY', it will not be inserted, the database cannot persist anything from computed column that raised an exception. So we will just roll our own cast that doesn't raise exception.

To wit(just translate this to 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;

Please do note that catching exception in UDF will not work yet in Sql Server, but Microsoft have plans to support that

This is now our non-exception-raising persisted computed column:

ALTER TABLE tbl ADD
numeric_equivalent  AS cast_as_int(a) persisted

Drop the existing index, then recreate it:

create index ix_num_equiv on tbl(numeric_equivalent)    

Now this query will become index-abiding-citizen, performant, and won't raise exception even the order of conditions is reversed:

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

To make it more performant, since the numeric_equivalent column doesn't raise any more exceptions, we have no more use for is_col_numeric, so just use this:

select * from tbl where numeric_equivalent = 0 

---

Do you like:

SELECT * FROM MY_TABLE 
WHERE REPLACE (MY_COLUMN, '0', NULL) IS NULL
AND MY_COLUMN IS NOT NULL;

---

This would also work in Oracle (but not in SQL Server):

REPLACE(column_name, '0') IS NULL

This will work in Oracle (and perhaps also in SQL Server, you will have to check):

LTRIM(column_name, '0') IS NULL

Alternatively, since it is a VARCHAR(6) column, you could also just check:

column_name IN ('0', '00', '000', '0000', '00000', '000000')

This is not pretty but it is probably the most efficient if there is an index on the column.

---

Building off KM's answer, you can do the same thing in Oracle without needing to create an actual table.

SELECT y.*
  FROM YourTable  y
  WHERE YourColumn IN
    (SELECT LPAD('0',level,'0') FROM dual CONNECT BY LEVEL <= 6)

or

SELECT y.*
  FROM YourTable  y
    INNER JOIN
     (SELECT LPAD('0',level,'0') zeros FROM dual CONNECT BY LEVEL <= 6) z
            ON y.YourColumn = z.zeros

I think this is the most flexible answer because if the maximum length of the column changes, you just need to change 6 to the new length.

---

How about using regular expression (supported by oracle, I think also MSSQL)

---

Another SQL version would be:

...
where len(COLUMN_NAME) > 0
 and len(replace(COLUMN_NAME, '0', '')) = 0

i.e., where there are more than 1 characters in the column, and all of them are 0. Toss in TRIM if there can be leading, trailing, or embedded spaces.

---

try this, which should be able to use and index on YourTable.COLUMN_NAME if it exists:

--SQL Server syntax, but should be similar in Oracle
--you could make this a temp of permanent table 
CREATE TABLE Zeros (Zero varchar(6))
INSERT INTO Zeros VALUES ('0')
INSERT INTO Zeros VALUES ('00')
INSERT INTO Zeros VALUES ('000')
INSERT INTO Zeros VALUES ('0000')
INSERT INTO Zeros VALUES ('00000')
INSERT INTO Zeros VALUES ('000000')

SELECT
    y.*
    FROM YourTable       y
        INNER JOIN Zeros z On y.COLUMN_NAME=z.Zero

EDIT
or even just this:

SELECT
    *
    FROM YourTable
    WHERE COLUMN_NAME IN ('0','00','000','0000','00000','000000')

building off of Dave Costa's answer:

Oracle:

SELECT
    *
    FROM YourTable
    WHERE YourColumn IN
        (SELECT LPAD('0',level,'0') FROM dual CONNECT BY LEVEL <= 6)

SQL Server 2005 and up:

;WITH Zeros AS
(SELECT 
     CONVERT(varchar(6),'0') AS Zero
 UNION ALL
 SELECT '0'+CONVERT(varchar(5),Zero)
     FROM Zeros
     WHERE LEN(CONVERT(varchar(6),Zero))<6
)
select Zero from Zeros
SELECT
    y.*
    FROM YourTable       y
    WHERE y.COLUMN_NAME IN (SELECT Zero FROM Zeros)