Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: 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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    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".

  3. #3
    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

    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  5. #5
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    > 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
    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 13:28.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  9. #9
    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..)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    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 ..

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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!

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •