Oracle empty string ('') is the same as NULL

  • In Oracle, the NVL function is equivilent to the ISNULL function in SQL.

    One of the wierd things in Oracle is that an empty string ('') is considered NULL, which is not the same behavior that SQL treats an empty string.

    For me, in amostly TSQL background, an empty string is not the same as NULL, and this trips up some code portability issues once in a while.

    so things like testing NVL(SomeColumn,'') = '' in a WHERE statement is the same as saying NVL(SomeColumn,'') = NULL, which warps results;

    Is there an enviromental setting in Oracle that I am not aware of that might make an empty string treated as something other than NULL?

    here's an orcle statement for an example:

    /*

    --results

    TheVal TheCalc

    (null) IT IS NULL --expected

    (null) IT IS NULL --NOT expected

    IT IS NOT NULL --expected

    */

    SELECT

    THEVAL,

    CASE

    WHEN (NVL(THEVAL,'')) IS NULL THEN 'IT IS NULL'

    ELSE 'IT IS NOT NULL'

    END AS THECALC

    FROM

    (SELECT NULL AS THEVAL FROM DUAL UNION ALL

    SELECT '' AS THEVAL FROM DUAL UNION ALL

    SELECT' ' AS THEVAL FROM DUAL );

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (10/11/2010)


    In Oracle, the NVL function is equivilent to the ISNULL function in SQL.

    One of the wierd things in Oracle is that an empty string ('') is considered NULL, which is not the same behavior that SQL treats an empty string.

    For me, in amostly TSQL background, an empty string is not the same as NULL, and this trips up some code portability issues once in a while.

    so things like testing NVL(SomeColumn,'') = '' in a WHERE statement is the same as saying NVL(SomeColumn,'') = NULL, which warps results;

    Is there an enviromental setting in Oracle that I am not aware of that might make an empty string treated as something other than NULL?

    You are correct. Oracle treats zero-length strings as Nulls.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • ... and, no, there is no setting that I know of that will make it otherwise.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I would add that it depends on how you define your string. If column is defined as varchar2(1) then empty string will be treated as NULL in Oracle. However, if you define column as char(1), empty string '' becomes ' ' (since char type is a blank padded string).

  • And ' ' is no longer treated as NULL...

  • BowieRules! (10/15/2010)


    I would add that it depends on how you define your string. If column is defined as varchar2(1) then empty string will be treated as NULL in Oracle. However, if you define column as char(1), empty string '' becomes ' ' (since char type is a blank padded string).

    so... it is not an empty string, huh? 😀

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • BowieRules! (10/15/2010)


    And ' ' is no longer treated as NULL...

    :blink: Really?

    SQL> select version from v$instance;

    VERSION

    -----------------

    11.1.0.7.0

    SQL>

    SQL> variable XXX varchar2(20)

    SQL> exec :XXX := ''

    PL/SQL procedure successfully completed.

    SQL> select :XXX from dual;

    :XXX

    --------------------------------

    SQL> select decode(:XXX,NULL,'IS NULL','IS NOT NULL') from dual;

    DECODE(:XXX,NULL,'ISNULL','ISNOT

    --------------------------------

    IS NULL

    SQL>

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • If you define that variable as char(1) or char(20) instead of varchar2, you will see that it won't be treated as null. That's what I was trying to specify...

  • BowieRules! (10/18/2010)


    If you define that variable as char(1) or char(20) instead of varchar2, you will see that it won't be treated as null. That's what I was trying to specify...

    Since by definition you cannot store an empty string in a char datatype the whole discussion around that particular point is plain non-sense, don't you think so? 🙂

    By the way, the test I posted was in regards to your other statement 😉

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • No, I don't think so... Char data type is also a string data type, and person posting original question was looking for a way to distinguish between NULL and empty string, so it could be one of the solutions to make that distinction, in my opinion.

  • BowieRules! (10/18/2010)


    No, I don't think so... Char data type is also a string data type, and person posting original question was looking for a way to distinguish between NULL and empty string, so it could be one of the solutions to make that distinction, in my opinion.

    Please read original post again - I think you missunderstood it.

    Having said that, ask yourself this question: Can I retrieve a Null value from a CHAR datatype?

    You cannot, don't you? so where is the Null value?

    Remember the Rule #1 of Holes. If you find yourself in a hole, stop digging. 🙂

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • I think you misunderstood my point. You CAN select null from CHAR column:

    SQL> create table chr(x char(1));

    Table created.

    SQL> insert into chr values(null);

    1 row created.

    SQL> insert into chr values(null);

    1 row created.

    SQL> insert into chr values('A');

    1 row created.

    SQL> select count(*) from chr where x is null;

    COUNT(*)

    ----------

    2

  • Oh yes, you can but, a Null and an empty string will be both treated as Null

    - insert a Null

    - insert an empty-string

    - insert a letter

    - count nulls... two!

    SQL> create table chr(x char(1));

    Table created.

    SQL> insert into chr values(null);

    1 row created.

    SQL> insert into chr values('');

    1 row created.

    SQL> insert into chr values('A');

    1 row created.

    SQL> select count(*) from chr where x is null;

    COUNT(*)

    ----------

    2

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • In SQL in Oracle they will be treated is null, but not in PL/SQL:

    DECLARE

    col1 CHAR(1);

    BEGIN

    col1 := '';

    DBMS_OUTPUT.PUT_LINE('Length :'||TO_CHAR(LENGTH(col1)));

    IF (col1 IS NULL) THEN

    dbms_output.put_line('Treated as NULL');

    END IF;

    IF (col1 = '') THEN

    dbms_output.put_line('Treated as EMPTY STRING');

    END IF;

    IF (col1 = ' ') THEN

    dbms_output.put_line('Treated as space');

    END IF;

    END;

    /

  • SQL>

    SQL> set serveroutput on

    SQL> declare

    2 XXX varchar2(10);

    3 MSG varchar2(50);

    4 begin

    5 XXX := '';

    6 select decode(XXX,NULL,'IS NULL','IS NOT NULL') into MSG from dual;

    7 dbms_output.put_line(MSG);

    8 XXX := Null;

    9 select decode(XXX,NULL,'IS NULL','IS NOT NULL') into MSG from dual;

    10 dbms_output.put_line(MSG);

    11 end;

    12 /

    IS NULL

    IS NULL

    PL/SQL procedure successfully completed.

    SQL>

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply