Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    19

    Unanswered: How to set Tab as column separator in SQL*Plus?

    Any advice or workarounds, greatly appreciated.

    Cheers,

    Pei

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How to set Tab as column separator in SQL*Plus?

    Originally posted by peisiong
    Any advice or workarounds, greatly appreciated.

    Cheers,

    Pei
    You could do this:

    SQL> column tab new_value tab
    SQL> select chr(9) tab from dual;

    T
    -


    SQL> set colsep "&tab"

  3. #3
    Join Date
    Apr 2003
    Posts
    19

    Re: How to set Tab as column separator in SQL*Plus?

    [QUOTE][SIZE=1]Originally posted by andrewst
    Thanks Andrew!!!

  4. #4
    Join Date
    Apr 2003
    Posts
    19

    tab as col separator

    Hi

    I have done what you have suggested and the spooled file looks ok when I open it in EXCEL.

    But when I open it in a text editor, there seems to be spaces (lots of them) in place of tab.

    Any idea why?

    Cheers,

    Pei Siong

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: tab as col separator

    Originally posted by peisiong
    Hi

    I have done what you have suggested and the spooled file looks ok when I open it in EXCEL.

    But when I open it in a text editor, there seems to be spaces (lots of them) in place of tab.

    Any idea why?

    Cheers,

    Pei Siong
    There are spaces, as well as the delimiting TABs, because of the way SQL Plus formats the data WITHIN the columns, e.g.:

    ____DEPTNO|DNAME_________|LOC_________
    ----------|--------------|-------------
    ________10|ACCOUNTING____|NEW_YORK____
    ________20|RESEARCH______|DALLAS______
    ________30|SALES_________|CHICAGO_____
    ________40|OPERATIONS____|BOSTON______

    (I have changed spaces to '_' and tabs to '|' so you can see better).

    I'm not aware of any way to change this behaviour. A common way to get tab-delimited output without spaces is to select it that way:

    SELECT deptno||CHR(9)||dname||CHR(9)||loc AS record
    FROM dept;

    RECORD
    ---------------------------------------------------------------------
    10|ACCOUNTING|NEW YORK
    20|RESEARCH|DALLAS
    30|SALES|CHICAGO
    40|OPERATIONS|BOSTON

    Use SET TRIMSPOOL ON to remove the trailing spaces on the last column.

  6. #6
    Join Date
    Apr 2003
    Posts
    19

    tab as column delimiter

    Hi andrew,

    Thanks for your help. If I have 100+ columns in my select statement, will using concatenate affect the performance of the select?

    Cheers,

    Pete

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: tab as column delimiter

    Originally posted by peisiong
    Hi andrew,

    Thanks for your help. If I have 100+ columns in my select statement, will using concatenate affect the performance of the select?

    Cheers,

    Pete
    Not as far as I know.

Posting Permissions

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