OraFAQ Forum: SQL & PL/SQL » how to find record with all 0 values records

Home » SQL & PL/SQL » SQL & PL/SQL » how to find record with all 0 values records

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to find record with all 0 values records [message #627805]

Mon, 17 November 2014 04:16

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

I need a query to find out the records with all 0,

create table t1 (
id number,
val varchar2(10)
);

insert into t1 values (1,'0');
insert into t1  values (2,'01');
insert into t1  values (3,'00');
insert into t1 values (4,'0000');
insert into t1  values(5,'010');
commit;

From that table i need the output as like

1 0
3 00
5 0000

Report message to a moderator

Re: how to find record with all 0 values records [message #627806 is a reply to message #627805]

Mon, 17 November 2014 04:21

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

Is this a college homework question? You need to show what SQL you have tried so far. One approach would be to use regular expressions.

Report message to a moderator

Re: how to find record with all 0 values records [message #627808 is a reply to message #627806]

Mon, 17 November 2014 04:30

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

you can do it with replace

Report message to a moderator

Re: how to find record with all 0 values records [message #627809 is a reply to message #627806]

Mon, 17 November 2014 04:30

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

actually this is not college homework. I have to find out record of such criteria. so i make the dummy data and table here/

Report message to a moderator

Re: how to find record with all 0 values records [message #627811 is a reply to message #627809]

Mon, 17 November 2014 04:31

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

so try using replace.

Report message to a moderator

Re: how to find record with all 0 values records [message #627812 is a reply to message #627809]

Mon, 17 November 2014 04:31

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

Yes i get the solution with regular expression.

SELECT * FROM t1  WHERE REGEXP_LIKE (val, '^[0]*$') order by ID;

Report message to a moderator

Re: how to find record with all 0 values records [message #627813 is a reply to message #627809]

Mon, 17 November 2014 04:34

sandeep_orafaq
Messages: 88
Registered: September 2014

Member

1 0
3 00
5 0000 --- with 5 you got 0000 ? is it ok?

Report message to a moderator

Re: how to find record with all 0 values records [message #627814 is a reply to message #627813]

Mon, 17 November 2014 04:39

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

sandeep_orafaq wrote on Mon, 17 November 2014 04:34

1 0
3 00
5 0000 --- with 5 you got 0000 ? is it ok?

sorry it will be 4 not 5. I said i made a dummy data.

Report message to a moderator

Re: how to find record with all 0 values records [message #627816 is a reply to message #627808]

Mon, 17 November 2014 05:10

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

cookiemonster wrote on Mon, 17 November 2014 11:30

you can do it with replace

Or translate.

Report message to a moderator

Re: how to find record with all 0 values records [message #627817 is a reply to message #627812]

Mon, 17 November 2014 05:13

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

mmi78 wrote on Mon, 17 November 2014 11:31

Yes i get the solution with regular expression.
SELECT * FROM t1  WHERE REGEXP_LIKE (val, '^[0]*$') order by ID;

You use a steamroller to swat a fly, not very efficient.

Report message to a moderator

Re: how to find record with all 0 values records [message #627818 is a reply to message #627817]

Mon, 17 November 2014 05:24

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

Michel Cadot wrote on Mon, 17 November 2014 05:13

mmi78 wrote on Mon, 17 November 2014 11:31
Yes i get the solution with regular expression.
SELECT * FROM t1  WHERE REGEXP_LIKE (val, '^[0]*$') order by ID;
You use a steamroller to swat a fly, not very efficient.

Please tell me the efficient way

Report message to a moderator

Re: how to find record with all 0 values records [message #627820 is a reply to message #627818]

Mon, 17 November 2014 05:56

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

REPLACE or TRANSLATE as already said: if you remove all 0 the result should be NULL.

Report message to a moderator

Re: how to find record with all 0 values records [message #627821 is a reply to message #627820]

Mon, 17 November 2014 06:07

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

replace or translate will remove all 0. But my desire output is only those rows which have all 0, it any rows contains 0 and other values, it will not display. Say in my example 5 010 will not display.

Report message to a moderator

Re: how to find record with all 0 values records [message #627822 is a reply to message #627821]

Mon, 17 November 2014 06:17

tigsav
Messages: 49
Registered: April 2012

Member

No it will Not ,Select Query will never modify the Data.

select id, val from t1 where lpad(val, 10, '0') = lpad('0', 10, '0');

select id, val from t1 where replace(val, 0) IS NULL;

Report message to a moderator

Re: how to find record with all 0 values records [message #627823 is a reply to message #627822]

Mon, 17 November 2014 06:21

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please do not give solution on basic queries, let people learn.

Now, try to do it with TRANSLATE. Wink

Report message to a moderator

Re: how to find record with all 0 values records [message #627824 is a reply to message #627822]

Mon, 17 November 2014 06:21

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

tigsav wrote on Mon, 17 November 2014 06:17

No it will Not ,Select Query will never modify the Data.
select id, val from t1 where lpad(val, 10, '0') = lpad('0', 10, '0');

select id, val from t1 where replace(val, 0) IS NULL;

2nd query is awesome. Thanks a lot

Report message to a moderator

Re: how to find record with all 0 values records [message #627825 is a reply to message #627824]

Mon, 17 November 2014 06:24

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is what cookiemonster and I meant.
It would better you found it by yourself, now try to do it with TRANSLATE, this is the same principle.

Report message to a moderator

Re: how to find record with all 0 values records [message #627826 is a reply to message #627825]

Mon, 17 November 2014 06:28

mmi78
Messages: 31
Registered: April 2013
Location: dhaka

Member

select id, val from t1 where trim(TRANSLATE(val, '0', ' ')) is null

Am i correct Michel Cadot

Report message to a moderator

Re: how to find record with all 0 values records [message #627828 is a reply to message #627820]

Mon, 17 November 2014 06:50

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel Cadot wrote on Mon, 17 November 2014 06:56

REPLACE or TRANSLATE as already said: if you remove all 0 the result should be NULL.

Yes, but how we say in math, it is necessary however not sufficient condition.

SY.

Report message to a moderator

Re: how to find record with all 0 values records [message #627830 is a reply to message #627826]

Mon, 17 November 2014 06:57

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

mmi78 wrote on Mon, 17 November 2014 13:28

select id, val from t1 where trim(TRANSLATE(val, '0', ' ')) is null

Am i correct Michel Cadot

You can avoid TRIM function:

SQL> select id, val from t1 where TRANSLATE(val, ' 0', ' ') is null;
        ID VAL
---------- ----------
         1 0
         3 00
         4 0000

3 rows selected.

If a character in the second parameter has no character at the same place in the third parameter then this character is removed from the first parameter string (val).

Report message to a moderator

Re: how to find record with all 0 values records [message #627831 is a reply to message #627828]

Mon, 17 November 2014 07:01

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Solomon Yakobson wrote on Mon, 17 November 2014 13:50

Michel Cadot wrote on Mon, 17 November 2014 06:56

REPLACE or TRANSLATE as already said: if you remove all 0 the result should be NULL.

Yes, but how we say in math, it is necessary however not sufficient condition.

SY.

Assuming VAL is not null. Wink

Even then, does no character, so no 0 counts as all characters are 0? /forum/fa/7257/0/

Report message to a moderator

Re: how to find record with all 0 values records [message #627836 is a reply to message #627831]

Mon, 17 November 2014 07:10

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel Cadot wrote on Mon, 17 November 2014 08:01

Even then, does no character, so no 0 counts as all characters are 0?

No, we can't make any assumptions since NULL means value is unknown.

SY.

Report message to a moderator

Re: how to find record with all 0 values records [message #627843 is a reply to message #627836]

Mon, 17 November 2014 07:51

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Unless specifications say NULLs are not NULLs but empty string as the column can't be NULL but can be empty.
Damn, Oracle does not support that... we have to convert all VARCHAR2 to CLOB. Laughing

Report message to a moderator

Previous Topic:	Date comparison
Next Topic:	fetch data based on Column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 02:48:57 CDT 2024