Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    9

    Question Unanswered: Updating columns with double quotes

    hi

    How do Update char/varchar columns in SQL 2000 so that
    i can add double quotes for all values i the column

    eg:
    Name
    aa
    bb
    cc

    should become

    Name
    "aa"
    "bb"
    "cc"

    Is it possible for only specific colums or has to be done for the entire table?both solutions welcome.

    Thanks
    Romshan

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Code:
    update <your table name>
         set <your attribute name> = '"' + <your attribute name> + '"'
    Out of curiosity, why would you want to do this?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jul 2003
    Posts
    9
    The problem is this:

    I have to export data from SQL2000 to a .csv file using only batch/dos commands. I cannot use dts or any other wizard/language.

    In order to generate a .csv file i have to give "," as the column delimeter. But my data also contains embedded "," like 'aaa,bbb'

    While exporting the data only aaa,bbb without any surrounding quotes.
    So when opened in excel the comma is taken as a delimter and not as a data. and i get two columns instead of one.

    There is a option in dts where you can specify text qualifier.
    Is there any workaround to do the similar task?

    Same problem applies while importing too.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    first, DTS can be run from the command line, DTSRUN.EXE.
    2nd, BCP can support special file formats where you can specify one filed delimiter as ',' a 2nd delimiter as '",' and a 3rd delimiter as ',"'.

    Both of these topics are coverd in Book Online. Post back once you have looked this over.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jul 2003
    Posts
    9
    explored both options.. here are the findings..

    I cannot use DTSrun.exe as i wil have to create an import/export package, which i shouldnt.
    BCP format files cannot be used because there are n number tables and it is not feasible to create format files for each table.

    Finally i have to deliver a batch file for the import/export operations.
    Please suggest some remedy ?

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    So you have two problems, you need to deal with double quotes on data going into the DB and commas in the data extracted from the DB. You can't use DTS and the fetures of BCP ment to deal with this are to cumbersome. Sounds like you need to write a bit of code that can run at the CMD prompt to modify your data both in and out bound. Maybe PERL would do the trick.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use '"' + CharacterField + '"'.
    Each doublequote is surrounded by single quotes.

    blindman

Posting Permissions

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