Null equals white?

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 14 Nov 2007 11:30 am

Hello guys.

The link below I found a brief exchange of ideas between dr_gori and charlesmafra about how Oracle handles null and blank ''.

viewtopic.php?p=357 Now tell me: how am I supposed to do for the Oracle identify a field blank? (In the case of MSSQL server, null and '' are two different things).
It seems to me that when I save a column with value blank (not null) Oracle interprets null.

See the code suggested by charlesmafra:
Code: Select all
DECLARE
        A VARCHAR2(10);
        BEGIN
          --A:='';
          IF A is NULL
          THEN DBMS_OUTPUT.PUT_LINE('Esse é null!');
          END IF;
       END;
why Oracle does not recognize the white ''?For example
Code: Select all
DECLARE
        A VARCHAR2(10);
        BEGIN
          A:='';
          IF A like ''
          THEN DBMS_OUTPUT.PUT_LINE('Esse é null!');
          END IF;
       END;
GilberttJR
Location: São Paulo

Poston Wed, 14 Nov 2007 11:33 am

Composing better the last code I put:
Code: Select all
DECLARE
A VARCHAR2(10);
BEGIN
A:='';
IF A like ''
THEN DBMS_OUTPUT.PUT_LINE('Esse é VAZIO!');
END IF;
END;
GilberttJR
Location: São Paulo

Poston Wed, 14 Nov 2007 1:35 pm

And ai beleza?

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

Code: 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 my Valley '' variable, here comes the first question, we know that '' is different from '' if that's true why dont entered in point 1? well, I believe that the não has come porque internally the point 1 works the same way as the point 2, the Bank ends up replacing '' by null.
In point 3 I just did a consistency checking if ' ' is null or not.

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Tue, 20 Nov 2007 2:38 pm

aí kara ... I did the same test using the NVL to compare!


Code: 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 got the following result:
NVL (' test ', ' NULL ' value)-------------------------NVL TEST (' ', ' NULL ' value)--------------------NVL (NULL, ' NULL ' value)----------------------NULL value NVL ('', ' NULL ' value)--------------------NULL value
vlw! : shock:
Renan Orati
Location: São José do Rio Preto - SP


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 5 guests