Is NULL equal to white?

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
GilberttJR
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 8
Joined: Fri, 26 Oct 2007 9:33 am
Location: São Paulo

Hey guys.

In the link below I found a brief exchange of ideas between Dr_Gori and Charlesmafra about how Oracle treats null and blank ''.
http://en.glufke.net/oracle/viewtopic.php?p=357
Now tell me: How would I do for Oracle to identify a blank field? (In the case of MSSQL Server, null and '' are two different things).
to which it seems to me when saving a column with a blank value (and no null) Oracle interprets with null.

See the code suggested by CharlesMafra:

Select all

 
DECLARE  
        A VARCHAR2(10);  
        BEGIN  
          --A:='';  
          IF A is NULL  
          THEN DBMS_OUTPUT.PUT_LINE('Esse é null!');  
          END IF;  
       END; 
Why does oracle recognize white ''? For example

Select all

 
DECLARE  
        A VARCHAR2(10);  
        BEGIN  
          A:='';  
          IF A like '' 
          THEN DBMS_OUTPUT.PUT_LINE('Esse é null!');  
          END IF;  
       END; 
GilberttJR
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 8
Joined: Fri, 26 Oct 2007 9:33 am
Location: São Paulo

Better the last code I put:

Select all

 
DECLARE  
A VARCHAR2(10);  
BEGIN  
A:='';  
IF A like ''  
THEN DBMS_OUTPUT.PUT_LINE('Esse é VAZIO!');  
END IF;  
END; 
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

What's up?

face in my point of view '' is equal to null yes, below is tests I did ..

Select all

 
SQL> ED 
Wrote file afiedt.buf 
 
  1  DECLARE 
  2     v_testeC VARCHAR2(1) := ' '; 
  3  BEGIN 
  4     /* PONTO 1 */ 
  5     IF (v_testeC <> '') THEN 
  6        dbms_output.put_line('C não é vazio, pto 1'); 
  7     END IF; 
  8     /* PONTO 2 - PROPOSITALMENTE*/ 
  9     IF (v_testeC <> NULL) THEN 
 10        dbms_output.put_line('C não é vazio, pto 2'); 
 11     END IF; 
 12     /* PONTO 3 - PROPOSITALMENTE*/ 
 13     IF (v_testeC IS NULL) THEN 
 14        dbms_output.put_line('C é vazio, pto 3'); 
 15     ELSE 
 16        dbms_output.put_line('C não é vazio, pto 3'); 
 17     END IF; 
 18* END; 
SQL> / 
C não é vazio, pto 3 
 
PL/SQL procedure successfully completed. 
In this case of mine Variable Vale '', here comes the first question, we know that '' is different from '', if this is true because it did not enter point 1? Well, I believe that has not entered because internally point 1 works in the same way as point 2, the bank ends up replacing '' by null ..
in point 3 I only made a consistency checking if '' is null or not ..

[] 's
Renan Orati
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 90
Joined: Thu, 23 Aug 2007 3:40 pm
Location: São José do Rio Preto - SP

aí Kara ... I did the same test ... Using the NVL to compare!

Select all

 
SELECT NVL('TESTE', 'VALOR-NULO') FROM DUAL; 
 
SELECT NVL(' ', 'VALOR-NULO') FROM DUAL; 
 
SELECT NVL(NULL, 'VALOR-NULO') FROM DUAL; 
 
SELECT NVL('', 'VALOR-NULO') FROM DUAL; 
and obtained the following result:

Select all

NVL('TESTE','VALOR-NULO') 
------------------------- 
TESTE 
  
NVL('','VALOR-NULO') 
-------------------- 
  
NVL(NULL,'VALOR-NULO') 
---------------------- 
VALOR-NULO 
  
NVL('','VALOR-NULO') 
-------------------- 
VALOR-NULO

vlw! : Shock:
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 14 guests