| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-05-05, 10:35
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
|
String Comparisson
|
|
It's been awhile, and Probably it has to do with a setting or a datatype...BUT
In what scenario would
'716232 ' = '716232'
Be true, and/or not true.
Note the 2 spaces at the end of the first string, assuming the data is both stored in 2 different columns
I've only ever used varchar2 data types, and I'm wondering if the data stored in 1 column was char, if char reatins the padding, where varchar2 would not.
Oh, and what's the difference between varchar and varchar2? Backward compatability?
Sorry for the fundamental question
|
|
01-05-05, 10:53
|
|
Registered User
|
|
Join Date: Nov 2003
Location: down on the cube farm, left then right then another left
Posts: 467
|
|
The 2 will never be equal unless you manipulate one ie. RTRIM('716232 ') = '716232'.
From my experience I think you are dealing with a char datatype '716232 ' and a varchar2.
You are correct, char retains padding varchar2 does not. Varchar2 is the "modern" method to use.
varchar and varchar2 are synonymus http://download-west.oracle.com/docs...ypes.htm#10733
__________________
NOTE: Please disregard the label "Senior Member".
|
|
01-05-05, 10:58
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
|
|
Got it.
car reatins the padding. So when A Pipe "delimted" file was sent, they loaded it as is...and the columns is defined as char(10).
Unfortunatley, the DB2 Pipe delimted file was not stripped of its spaces so the Pipe cam out as
....|71623 |....
And the table they are joininng to, I can only assume, is varchar2, which does not retain padding.
Oh, and I also found out the difference between varchar and varchar2 is actually version specific.
Brian Peasland at http://searchoracle.techtarget.com
Quote:
|
Actually, Oracle Corp. has changed the semantics of the VARCHAR2 datatype. Currently, in Oracle 9i, when you insert a zero length string into a VARCHAR2 column, Oracle treats this the same as if you insert a NULL value into that column. But it wasn't always this way. Previous versions of the database treated zero length strings and NULL values as two distinctly different items.
|
|
|
01-05-05, 11:40
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
Quote:
|
Actually, Oracle Corp. has changed the semantics of the VARCHAR2 datatype. Currently, in Oracle 9i, when you insert a zero length string into a VARCHAR2 column, Oracle treats this the same as if you insert a NULL value into that column. But it wasn't always this way. Previous versions of the database treated zero length strings and NULL values as two distinctly different items.
|
That doesn't sound right to me. AFAIK, Oracle has never distinguished between a zero-length string and a NULL (unfortunately!). It can't, because it uses a string length of 0 to indicate that the column contains a NULL.
|
|
01-05-05, 12:00
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
I don't have an 8i environment set up anymore so I can't test it. but are you saying that Brian is mistaken?
I'd be suprised they published it and didn't check it.
|
|
01-05-05, 12:05
|
|
Registered User
|
|
Join Date: Sep 2004
Location: London, UK
Posts: 564
|
|
> That doesn't sound right to me
Me neither. I am pretty sure it's wrong. I haven't been near a pre-9i database in over a year though so can't prove it. The online documentation goes back as far as 7.3 and that still contains
Quote:
VARCHAR Datatype
The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype. However, in a future version of Oracle, the VARCHAR datatype might store variable-length character strings compared with different comparison semantics. Therefore, use the VARCHAR2 datatype to store variable-length character strings.
|
which I think it has said since they introduced the two VARCHAR types (version 6, IIRC). However I've never used a VARCHAR datatype so maybe there was once an undocumented feature.
> Oracle has never distinguished between a zero-length string and a NULL (unfortunately!)
Very occasionally I wish I could distinguish between NULL and '', but more often I think it is a welcome simplification not to have two kinds of 'empty'.
|
Last edited by WilliamR; 01-05-05 at 12:28.
|
01-05-05, 12:35
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
Quote:
|
Originally Posted by Brett Kaiser
I don't have an 8i environment set up anymore so I can't test it. but are you saying that Brian is mistaken?
I'd be suprised they published it and didn't check it.
|
Yes, I am saying that. Luckily(!) I still have 8i here:
Code:
SQL*Plus: Release 8.0.6.0.0 - Production on Wed Jan 5 17:27:58 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
SQL> create table t (id int, text varchar2(10));
Table created.
SQL> insert into t values (1, '');
1 row created.
SQL> select * from t where text is null;
ID TEXT
---------- ----------
1
If there's any other test you'd like me to run on 8i, let me know.
|
|
01-05-05, 12:41
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
Quote:
|
Originally Posted by WilliamR
> Oracle has never distinguished between a zero-length string and a NULL (unfortunately!)
Very occasionally I wish I could distinguish between NULL and '', but more often I think it is a welcome simplification not to have two kinds of 'empty'.
|
I suppose it would be hard to tell '' and NULL apart on a report or a screen! But logically it would make more sense, e.g. with LENGTH('') returning 0 instead of NULL. Probably it seems confusing only because we are so used to Oracle treating them the same.
|
|
01-05-05, 12:43
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
Nothing like testing. Thanks.
What does
select * from t where text = ''
Give you?
And may I have your permission to post your results to my blog?
With proper credit of course
Now I wonder what he means by previous versions...
Or if I misread the comments (WHAT? again...maybe I can become a truck driver..)
|
|
01-05-05, 12:52
|
|
Registered User
|
|
Join Date: May 2004
Location: Dominican Republic
Posts: 721
|
|
It also depends on the data actually ..
Code:
SQL@8i> variable x char(7)
SQL@8i> variable y varchar2(7)
SQL@8i>
SQL@8i> exec :x := '716232'
PL/SQL procedure successfully completed.
SQL@8i> exec :y := '716232 '
PL/SQL procedure successfully completed.
SQL@8i> select 1 from dual where :y = :x ;
1
---------
1
That evaluated to true, since CHAR are always right-padded with spaces. Those two string, while introduced initially different, they are the same on the db right now.
What they have seem to have changed, is the to_char( ) semantic when comparing to a string literal.
Code:
SQL@8i> select 1 from dual where '716232 ' = to_char( '716232' );
no rows selected
SQL@8i>
Oracle must be casting each value as their own data type ( string literal as char, while the to_char( ) function as a varchar2 ), thus this end false.
However, in 9i ..
Code:
SQL@9iR2> select 1 from dual where '716232 ' = to_char( '716232' );
1
---------
1
SQL@9iR2>
Perhaps this is what the author was refering to ..
|
|
01-05-05, 13:09
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
Quote:
|
Originally Posted by Brett Kaiser
Nothing like testing. Thanks.
What does
select * from t where text = ''
Give you?
And may I have your permission to post your results to my blog?
With proper credit of course
|
SQL> select * from t where text = '';
no rows selected
Feel free to use these results in your blog!
|
|
01-05-05, 14:07
|
|
Window Washer
|
|
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
|
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
String Comparisson
