Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    12

    Question Unanswered: Problem with CHAR data type

    I have a problem with CHAR data type. I create a table with column defined as CHAR,

    CREATE TABLE cuserids1(
    usercode CHAR(10),
    username CHAR(80)
    );

    INSERT INTO cuserids1 VALUES ('mlg', 'MANUEL');
    INSERT INTO cuserids1 VALUES ('dvt', 'DAVID');

    SELECT * FROM cuserids1
    WHERE usercode = (CASE WHEN (SELECT usercode FROM cuserids1 b WHERE b.usercode = 'mlg') = 'mlg' THEN 'mlg' ELSE 'public' END)

    SQL> SELECT * FROM cuserids1
    2 WHERE usercode = (CASE WHEN (SELECT usercode FROM cuserids1 b WHERE b.usercode = 'mlg') = 'mlg'
    THEN 'mlg' ELSE 'public' END);

    ninguna fila seleccionada

    the previous query does not return any row, the problem is the char type because if I use TRIM in WHERE clause
    (WHERE TRIM(usercode) =) the result is different!

    SELECT * FROM cuserids1
    WHERE TRIM(usercode) = (CASE WHEN (SELECT usercode FROM cuserids1 b WHERE b.usercode = 'mlg') = 'mlg' THEN 'mlg' ELSE 'public' END)


    SQL> SELECT * FROM cuserids1
    2 WHERE TRIM(usercode) = (CASE WHEN (SELECT usercode FROM cuserids1 b WHERE b.usercode = 'mlg') =
    'mlg' THEN 'mlg' ELSE 'public' END);

    USERCODE
    ----------
    USERNAME
    --------------------------------------------------------------------------------
    mlg
    MANUEL


    If a define a column named 'usercode' as VARCHAR2, there's no problem:

    CREATE TABLE cuserids1(
    usercode VARCHAR2(20),
    username CHAR(80)
    );


    SQL> SELECT * FROM cuserids1
    2 WHERE usercode = (CASE WHEN (SELECT usercode FROM cuserids1 b WHERE b.usercode = 'mlg') = 'mlg' THEN 'mlg' ELSE 'public' END);

    USERCODE
    ----------
    USERNAME
    --------------------------------------------------------------------------------
    mlg
    MANUEL


    The question is: which is the better option (CHAR or VARCHAR2) from efficient point of view?

    1) Declare column as CHAR and use TRIM function.
    2) Define column as VARCHAR2.

    Which difference are between CHAR and VARCHAR2?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Never, ever use CHAR. Never. Forget CHAR exists, you don't want it.

    Always use VARCHAR2.

    What's the difference? You have seen for yourself: a CHAR is always blank-padded to its full length, a VARCHAR2 is not. Presumably someone, somewhere, once in a blue moon, finds CHAR useful. 99.999% of the time, you don't want it, you want VARCHAR2.

  3. #3
    Join Date
    Mar 2006
    Posts
    12

    Talking Problem with CHAR data type

    Thanks for the answer.


    Quote Originally Posted by andrewst
    Never, ever use CHAR. Never. Forget CHAR exists, you don't want it.

    Always use VARCHAR2.

    What's the difference? You have seen for yourself: a CHAR is always blank-padded to its full length, a VARCHAR2 is not. Presumably someone, somewhere, once in a blue moon, finds CHAR useful. 99.999% of the time, you don't want it, you want VARCHAR2.

  4. #4
    Join Date
    Jan 2004
    Posts
    99
    char can be a pain in the a** , especially when developers use tools such as hibernate to map tables.....when you have two joining columns, the blank padded spaces causes nightmares and returns no data. Therefore as the gentleman pointed out stick to varchar2 this will also save you space.!

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    CHAR variables can be a way to format values in PL/SQL, avoiding a lot of RPAD expressions. Umm, I think that's it. (And I last used that over 5 years ago, and it was possibly a bit of a hack even then.) CHAR is provided solely as some kind of perverse ANSI compatibility feature.
    Last edited by WilliamR; 04-23-06 at 20:45.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    what's worse is when the developers attempt to map a CHAR column to a VARCHAR column and wonder why they don't get any hits.

    DOH!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Apr 2006
    Location
    india
    Posts
    8
    varchar2 also gives dynamic allocation of memory ,then Y to use char

Posting Permissions

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