Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2006
    Posts
    115

    Unanswered: good delimiter(s) used for bcp out

    i request to bcp out some tables, global edit it the content & bcp in back to another same structure tables

    any good delimiter(s) can be used to cater all kinds of data(including text datatype) .

    someone suggest to use ASCII NUL (\0) ... but it cant further proceed by unix command(e.g sed..)

    pls advise.

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    I generally use ; as delimiter, but if the data can contain all sorts of characters, I try to come up with some wierd combination of characters.
    #:# works fine in most cases. The combination of those three characters is rarely found in columns (at least in my databases).
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Most people use the bar symbol '|' and a few use tabs, it really depends on the likelyhood of these characters appearing in your data. If you use \0 then you'll find that few unix tools will work reliably with the resulting files. To me it's far better to be able to see the bcp data and be confident that it looks correct than to use weird control characters as delimiters because you think it's safer.

  4. #4
    Join Date
    Jul 2006
    Posts
    115
    Quote Originally Posted by mike_bike_kite
    Most people use the bar symbol '|' and a few use tabs, it really depends on the likelyhood of these characters appearing in your data. If you use \0 then you'll find that few unix tools will work reliably with the resulting files. To me it's far better to be able to see the bcp data and be confident that it looks correct than to use weird control characters as delimiters because you think it's safer.
    so how about \b (ie backspace)? do u think it's gd?

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    No - I think using any control character will just cause you (and everyone else who uses the file) to feel a lot of pain in the future. You should either use a simple character like '|' or a combination of characters as Martijnvs suggests.

  6. #6
    Join Date
    Jul 2006
    Posts
    115

    Wink

    oic ... btw, according to your experiences, what combination of delimiters is ok for almost all kinds of data including image data stored in sybase? e.g #:# ...

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    oic ... btw, according to your experiences, what combination of delimiters is ok for almost all kinds of data including image data stored in sybase? e.g #:# ...
    I personally wouldn't store image data in a database (see here) but I would store a file location. I'd guess that an image file could contain any sequence of byte codes and so no delimiter would work for bcp. I'll repeat that I am guessing here so perhaps there's some clever workaround.

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Often, the tilde (~) can be appropriate.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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