Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2006
    Posts
    13

    Unanswered: Trailing white space in output

    Hello,

    Suppose I have a table with two fields named a and b. Both are defined as char(8).

    Record 1 contains for a --> 'AA' and for b --> 'BB'

    When I perform a simple select on the table and specify that I want to use a colsep ';' and no tabs I get the following output:

    AA ;BB ;

    My question is: how do i remove the trailing white space in the ouput?

    Thanks!

    ps: formating is not 100% but it is AA + six blanks and BB + six blanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    You need to concatenate the columns, and perhaps if the columns are defined as CHAR(8), you may also need to use the "RTRIM" function:
    Code:
    SELECT RTRIM(col1)||';'||RTRIM(col2) FROM MyTable;

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Feb 2006
    Posts
    13
    RTRIM doesn't work on the output. To get the delimiter I use set colsep ';'.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    colsep does not work (RTRIM the column) even if you had VARCHAR2 columns.

    You need to do as I suggested above.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Another thing to take into consideration is to NEVER use char for a column, use varchar2. CHAR will cause nothing but problems and confusion in code.

    There are times that you are stuck with CHAR columns because it is a legacy system, but never use CHAR for new development. The same also applies to LONG.

    Just my 2 cents.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Feb 2006
    Posts
    13
    Quote Originally Posted by beilstwh
    Another thing to take into consideration is to NEVER use char for a column, use varchar2. CHAR will cause nothing but problems and confusion in code.

    There are times that you are stuck with CHAR columns because it is a legacy system, but never use CHAR for new development. The same also applies to LONG.

    Just my 2 cents.
    That's not the point here. I just used char as an example. The problem also arises when using a varchar2. I have not tested the solution of LKBrwn_DBA at this point but I am pretty confident it will work.

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    fyi, there was no clue this was a SQL*Plus question until the "set colsep" in your second post.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by WilliamR
    fyi, there was no clue this was a SQL*Plus question until the "set colsep" in your second post.

    FYI "colsep" was present in his first post (I want to use colsep).

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by beilstwh
    Another thing to take into consideration is to NEVER use char for a column, use varchar2. CHAR will cause nothing but problems and confusion in code.

    There are times that you are stuck with CHAR columns because it is a legacy system, but never use CHAR for new development. The same also applies to LONG.

    Just my 2 cents.
    Except maybe for CHAR(1) in some cases, when you want to implement a boolean value with 'Y' and 'N' and a check constraint for example. A varchar2 would be usable but still CHAR would be clearer in such a case IMHO.

    Furthermore, if one day Oracle does not treat empty strings as NULLs anymore (blessed be that day ), then having a CHAR(1) would forbid the empty string without a check constraint whereas a Varchar2(1) wouldn't. Might be useful in some cases.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  10. #10
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by LKBrwn_DBA

    FYI "colsep" was present in his first post (I want to use colsep).
    Oops, I missed that. Apologies.

    I think the fact that the title of the post was "Trailing white space in output" and it mentioned right-padding with CHAR columns suggested that the problem was to do with the unfortunate choice of datatype, when in fact it was about the fact that SQL*Plus displays output in columns.

    I don't see any advantage in CHAR(1) over VARCHAR2(1), even for Y/N values. I suppose it is shorter, but it still seems to me to complicate things because now you have two character types in use.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by RBARAER
    Furthermore, if one day Oracle does not treat empty strings as NULLs anymore (blessed be that day )
    I actually like that behaviour. If the input was an empty string you can be sure that the column is null. Querying for non empty values is much easier this way. I simply write WHERE col IS NULL instead of WHERE col IS NULL or col = ''

    But this is clearly a matter of taste

    Thomas

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by WilliamR
    I don't see any advantage in CHAR(1) over VARCHAR2(1), even for Y/N values.
    AFAIK CHAR(1) takes less space then VARCHAR2(1) (don't know if you meant that with "shorter")
    but it still seems to me to complicate things because now you have two character types in use.
    What do you mean with two character types? The handling of the columns is absolutely the same, there is no difference when using them (in code e.g.)

    Having said that, I usually use a NUMBER(1) for this, because 0 and 1 are language neutral. I don't have to check whether the value for "true" is Y,y,T or T (or maybe even something localized). Of course I can look it up in the dictionary (provided the column has a check constraint) but with a numeric value it's clear right away

    Regards
    Thomas

  13. #13
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    AFAIK CHAR(1) takes less space then VARCHAR2(1) (don't know if you meant that with "shorter")
    I meant that "CHAR" is less to type than "VARCHAR2". Was that what you meant? If you meant that CHAR values occupy less space in the database, umm, they don't.

    Even in the case where there is no functional difference, I think having a mixture of CHARs and VARCHAR2s that do exactly the same thing is an unneccessary complication. I suppose you could argue that they make indicator columns stand out (if that is all you ever use CHAR for), but I'm not convinced.

    One application I had to deal with used -1 and 0 for TRUE and FALSE. I suggested to the developer that -1 for TRUE was a bit surprising and he said that was the way it worked "in code". It turns out that in the wacky world of Visual Basic, -1 means TRUE.

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    don't user COLSEP, code as for one column like this:

    select
    a||';'||b||';'
    from table_name;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Yes, just as I suggested in my first post:
    You need to concatenate the columns, and perhaps if the columns are defined as CHAR(8), you may also need to use the "RTRIM" function:
    Code:
    SELECT RTRIM(col1)||';'||RTRIM(col2) FROM MyTable;

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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