Professional Web Applications Themes

non-tab delimited output? - MySQL

I have a decidedly ugly problem...I really want output with NO delimiters whatsoever for specialized legacy systems reasons. Can anyone tell me if it's possible to get mysql to return results without the tabs? On a WindowsXP box, I am currently using: mysql -uexport -px --force -N hh < c:\batch\getFedexFrom.sql > P:\ship\FEDEX\fedexFROM.txt where getFedexFrom.sql is simply select * from hh.fedexfrom I'd rather do it all from mysql if possible, rather than having to do a 2nd sweep with some kind of awk. Any suggestions would be much appreciated! Tyler Style tyler AT healthy habits web DOT com...

  1. #1

    Default non-tab delimited output?

    I have a decidedly ugly problem...I really want output with NO
    delimiters whatsoever for specialized legacy systems reasons. Can
    anyone tell me if it's possible to get mysql to return results without
    the tabs? On a WindowsXP box, I am currently using:

    mysql -uexport -px --force -N hh < c:\batch\getFedexFrom.sql >
    P:\ship\FEDEX\fedexFROM.txt

    where getFedexFrom.sql is simply

    select * from hh.fedexfrom

    I'd rather do it all from mysql if possible, rather than having to do a
    2nd sweep with some kind of awk.

    Any suggestions would be much appreciated!

    Tyler Style



    tyler AT
    healthy habits web
    DOT
    com

    tyler.style@gmail.com Guest

  2. #2

    Default Re: non-tab delimited output?

    com wrote:
     

    select '~'||trim(A)||'~'||trim(B)||'~' as a from a;
    +-------+
    | A |
    +-------+
    | ~A~B~ |
    +-------+



    --
    Michael Austin.
    DBA Consultant
    Michael Guest

  3. #3

    Default Re: non-tab delimited output?

     

    Thank you...but it didn't work for me. It output a column of 1's. I'm
    actually not sure what the pipes and tildes are supposed to doing
    there, so perhaps I'm not adjusting for something I should be.

    Logos Guest

  4. #4

    Default Re: non-tab delimited output?

     
    >
    > Thank you...but it didn't work for me. It output a column of 1's. I'm
    > actually not sure what the pipes and tildes are supposed to doing
    > there, so perhaps I'm not adjusting for something I should be.[/ref]

    Post an example of the output you want.


    $ mysql test -N -B -e "select col1,col2,col3 from tab"
    Value1 Value2 Value3

    $ mysql test -N -B -e "select concat(col1,col2,col3) from tab"
    Value1Value2Value3


    Regards
    Dimitre




    Radoulov, Guest

  5. #5

    Default Re: non-tab delimited output?

    Logos wrote: 
    >
    >
    > Thank you...but it didn't work for me. It output a column of 1's. I'm
    > actually not sure what the pipes and tildes are supposed to doing
    > there, so perhaps I'm not adjusting for something I should be.
    >[/ref]

    the || (double pipe) is ansi-standard to "concat" strings together. The ~
    (tilde) is merely a field seperator. If omitted, then you must make sure you
    data fields are well defined and ALWAYS the same length. Otherwise, when you
    import it into something else, how do you where to break it?

    eg. col-a int, col-b int
    col-a col-b
    123 45678
    1234 5678

    in this example, there is no way to know how many "characters" are in col-a or col-b

    Using a mysql-specific function to replace the ansi-standard, you can use:

    select concat('~',trim(A),'~',trim(b),'~') as a from a;
    note, if the columns are integers, you can remove the trim() function - this is
    used to ensure no leading/trailing spaces.

    I try to use ansi-standard syntax as it can be translated from one database
    engine to another fairly easy. If you use database specific functions - when
    you do need to move - you must recode everything because you were not ansi-standard.


    --
    Michael Austin.
    DBA Consultant
    Michael Guest

  6. Moderated Post

    Default Re: non-tab delimited output?

    Removed by Administrator
    Logos Guest
    Moderated Post

  7. Moderated Post

    Default Re: non-tab delimited output?

    Removed by Administrator
    Peter Guest
    Moderated Post

  8. Moderated Post

    Default Re: non-tab delimited output?

    Removed by Administrator
    Michael Guest
    Moderated Post

  9. #9

    Default Re: non-tab delimited output?

    That does work, thank you :)

    I chose VARCHAR over CHAR because of this excerpt from the MySQL online
    dox:
    "If a given value is stored into the CHAR(4) and VARCHAR(4) columns,
    the values retrieved from the columns are not always the same because
    trailing spaces are removed from CHAR columns upon retrieval."

    So I chose VARCHAR, and then made sure the insertion cose space-pads
    the field value up to the field length.


    Michael Austin wrote:
     [/ref][/ref]
     

    Logos Guest

  10. #10

    Default Re: non-tab delimited output?

    On 24 Nov 2006 07:14:59 -0800, Logos wrote: 

    .... which always puzzled me as it's precisely the OPPOSITE as one would
    expect from the type names (not to mention how every other RDBS I've
    worked with does it). You'd thing the "VAR" one would be the one that
    would strip trailing space and do it on the record write...

    --
    The pluses in my current job include laughing in the face of Nobel
    laureates who have just lost the only copy of their data. (Hey,
    I'm still a BOFH).
    -- Bob Dowling
    Peter Guest

  11. #11

    Default Re: non-tab delimited output?

    Peter H. Coffin wrote: 
    >
    >
    > ... which always puzzled me as it's precisely the OPPOSITE as one would
    > expect from the type names (not to mention how every other RDBS I've
    > worked with does it). You'd thing the "VAR" one would be the one that
    > would strip trailing space and do it on the record write...
    >[/ref]

    Others should add their commends to Michael's bug report here:

    http://bugs.mysql.com/bug.php?id=24424

    The more who complain about it the more likely it is to be corrected.
    Right now they're trying to claim this is a "feature request", not a bug.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  12. #12

    Default Re: non-tab delimited output?

    On Sun, 26 Nov 2006 08:45:41 -0500, Jerry Stuckle wrote: 
    >>
    >>
    >> ... which always puzzled me as it's precisely the OPPOSITE as one would
    >> expect from the type names (not to mention how every other RDBS I've
    >> worked with does it). You'd thing the "VAR" one would be the one that
    >> would strip trailing space and do it on the record write...
    >>[/ref]
    >
    > Others should add their commends to Michael's bug report here:
    >
    > http://bugs.mysql.com/bug.php?id=24424
    >
    > The more who complain about it the more likely it is to be corrected.
    > Right now they're trying to claim this is a "feature request", not a bug.[/ref]

    I'm sure it's a "feature request" because undoubtedly there are many,
    many applications built around and depending on this quirk that would
    entirely break if it were changed to be more standard, and the only way
    to accomodate them were to make it a configuration option.

    --
    "Doesn't everybody?" is a question that never expects an answer of "No."
    Peter Guest

  13. #13

    Default Re: non-tab delimited output?

    Peter H. Coffin wrote: 
    >>
    >>Others should add their commends to Michael's bug report here:
    >>
    >>http://bugs.mysql.com/bug.php?id=24424
    >>
    >>The more who complain about it the more likely it is to be corrected.
    >>Right now they're trying to claim this is a "feature request", not a bug.[/ref]
    >
    >
    > I'm sure it's a "feature request" because undoubtedly there are many,
    > many applications built around and depending on this quirk that would
    > entirely break if it were changed to be more standard, and the only way
    > to accomodate them were to make it a configuration option.
    >[/ref]

    Possible - but it's also a direct violation of the SQL standards.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Delimited Lists
    By CSD_UK in forum Coldfusion - Getting Started
    Replies: 8
    Last Post: December 7th, 04:08 PM
  2. tab delimited text
    By Jacob Farber in forum Macromedia Director Lingo
    Replies: 4
    Last Post: November 30th, 06:24 AM
  3. Tab-delimited or CSV - Performance difference???
    By Laphan in forum ASP Database
    Replies: 5
    Last Post: August 30th, 06:05 PM
  4. split a tab delimited string
    By Ajit in forum ASP.NET General
    Replies: 6
    Last Post: July 29th, 03:32 PM
  5. generate CSV or comma delimited
    By shank in forum ASP
    Replies: 4
    Last Post: July 9th, 04:00 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139