Comparing Numbers against Strings in Oracle

Question

I have a query which returns the following sample data :-

SELECT * FROM VW_STATUSNEW

ID STATUS_1 STATUS_2
1  FAIL     1
2  FAIL     NULL
3  1        NULL
4  NULL     2
5  2        2
6  2        FAIL
7  NULL     NULL

Is it possible in Oracle to return all rows where STATUS_1 and STATUS_2 do not match. So, using the data above, the required results are :-

ID STATUS_1 STATUS_2
1  FAIL     1
2  FAIL     NULL
3  1        NULL
4  NULL     2
6  2        FAIL

The issue seems to be with comparing the word FAIL with a number, and catering for NULLs.

Why do u think it fails with a numeric and alphabet string? It does not since neither datatypes are numeric. Please understand the issue and don't blindly assume things..problem is only with null comparisons, that's all. — Kaushik Nayak
– Kaushik Nayak, Commented Apr 24, 2018 at 13:10

Gordon Linoff · Accepted Answer · 2018-04-24 12:07:27Z

2

The two statuses have the same type (there are strings in both columns). so comparison shouldn't be a problem. Here is one way:

SELECT *
FROM VW_STATUSNEW
WHERE (status1 <> status2) OR
      (not (status1 is null and status2 is null)) ;

answered Apr 24, 2018 at 12:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jay Shankar Gupta · Accepted Answer · 2018-04-24 13:18:24Z

1

SELECT *
FROM VW_STATUSNEW
WHERE STATUS_1 <> STATUS_2
OR (STATUS_1 IS NULL AND STATUS_2 IS NOT NULL)
OR (STATUS_1 IS NOT NULL AND STATUS_2 IS NULL);

OR

SELECT *
FROM VW_STATUSNEW
WHERE STATUS_1 <> STATUS_2
OR NOT (STATUS_1 IS NULL AND STATUS_2 IS NULL)

Output

ID  STATUS_1    STATUS_2
1   FAIL        1
2   FAIL        (null)
3   1           (null)
4   (null)      2
6   2           FAIL

Demo

http://sqlfiddle.com/#!4/3694b/5

edited Apr 24, 2018 at 13:18

answered Apr 24, 2018 at 12:11

Jay Shankar Gupta

6,1281 gold badge14 silver badges31 bronze badges

Comments

Ronnis · Accepted Answer · 2018-04-24 12:55:06Z

1

This is one of the few cases where DECODE is still useful, because according to decode, NULL is equal to null.

select * 
  from VW_STATUSNEW
 where decode(status_1, status_2, 1, 0) = 0;

answered Apr 24, 2018 at 12:55

Ronnis

12.9k3 gold badges37 silver badges56 bronze badges

Comments

Chrᴉz remembers Monica · Accepted Answer · 2018-04-24 12:14:58Z

0

with testtab as (select 'Fail' "STAT1", '1' "STAT2" from dual
union all select 'Fail', null from dual
union all select '1', null from dual
union all select null, '2' from dual
union all select '2', '2' from dual
union all select '2', 'FAIL' from dual
union all select null, null from dual)
select * from testtab
where nvl(STAT1,-1) != nvl(STAT2,-1);

returns

Stat1   Stat2 
Fail    1
Fail    null
1       null
null    2
2       FAIL

Stat1 = status_1 in your example.

nvl(value, altValue) returns altValue if value is null

answered Apr 24, 2018 at 12:14

Chrᴉz remembers Monica

1,9201 gold badge12 silver badges25 bronze badges

Comments

Istiaque Hossain · Accepted Answer · 2018-04-24 12:24:16Z

0

try with this:

SELECT 
  ID 
  ,STATUS_1 
  ,STATUS_2 
FROM VW_STATUSNEW 
where NVL(STATUS_1, '0') != NVL(STATUS_2, '0')

edited Apr 24, 2018 at 12:24

answered Apr 24, 2018 at 12:12

Istiaque Hossain

2,3671 gold badge20 silver badges30 bronze badges

4 Comments

Jay Shankar Gupta Over a year ago

it won't work NVL(null, '') will give you null and null can't be compared

David Faber Over a year ago

I'm not sure this would work the way you want it to - the empty string is equivalent to NULL in Oracle

Jay Shankar Gupta Over a year ago

Run this and check SELECT NVL(NULL,'') FROM DUAL

David Faber Over a year ago

What if STATUS_1 is 0 and STATUS_2 is NULL? They shouldn't match, but with your query they will.

Collectives™ on Stack Overflow

Comparing Numbers against Strings in Oracle

5 Answers 5

Comments

Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related