October 11, 2010 at 6:43 am
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
October 11, 2010 at 8:30 am
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.October 11, 2010 at 10:09 am
... and, no, there is no setting that I know of that will make it otherwise.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2010 at 1:19 pm
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).
October 15, 2010 at 1:20 pm
And ' ' is no longer treated as NULL...
October 18, 2010 at 6:17 am
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.October 18, 2010 at 6:24 am
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.October 18, 2010 at 8:59 am
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...
October 18, 2010 at 10:04 am
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.October 18, 2010 at 10:10 am
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.
October 18, 2010 at 10:21 am
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.October 18, 2010 at 10:35 am
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
October 18, 2010 at 11:09 am
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.October 18, 2010 at 11:30 am
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;
/
October 18, 2010 at 12:01 pm
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