Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    6

    Question Unanswered: Simple SQL semi colon problem

    Hi,
    I am trying to insert values into my database using the command window (im running DB2 version 8.1), but when my insert statement hits a semi colon (";") it considers that the terminating point

    How can i insert data into my database when the values themselves contain semi colons?? Is there an escape code or something??
    E.g.
    insert into "~TMPCLP361431" values ('document 1','bla bla bla; bla bla bla');

    Your help is much appreciated

  2. #2
    Join Date
    Mar 2004
    Posts
    205
    What is the datatype of the field ? I tried with char and varhcar, its working fine.

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    Sorry, previuosly I tried using JDBC and it worked. When I tried through DB2 command rpompt, its not working.

  4. #4
    Join Date
    Mar 2004
    Posts
    205
    Try this working fine :

    db2 insert into testchar1 values\(\'fgfg\;fdsg\'\)

    Escape sequence is '\'

  5. #5
    Join Date
    Aug 2004
    Posts
    6

    Red face

    Quote Originally Posted by SamCute
    Try this working fine :

    db2 insert into testchar1 values\(\'fgfg\;fdsg\'\)

    Escape sequence is '\'
    Hey,

    Thanks for your help, i tried this through the command window and it does not work, it still will not allow the ";" as part of the string. Its still interprets it as the end of the SQL insert. Im not sure what else to try . How does the escape character work?? And does it work in the command window??

  6. #6
    Join Date
    Aug 2004
    Posts
    6

    Wink

    Sorry, i am using the command center, so im trying to get this to work via the GUI. Cheers Please HELP!!!
    Last edited by reddevil; 08-04-04 at 18:13. Reason: Update

  7. #7
    Join Date
    Aug 2004
    Posts
    6

    Thumbs up

    Hey Everyone,
    Ive finally solved my own problem. Just thought id post it to let others know. Turned out to be really simple!!

    e.g.
    insert into "~TMPCLP361431" values ('document 1','bla bla bla; bla bla bla');
    in the command center stops at the ";"

    instead use:
    insert into "~TMPCLP361431" values ('document 1','bla bla bla'||CHR(59) ||'bla bla bla');

    Thanks for your help Sam, its much appreciated.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You might also try changing the Termination character from a semicolon (the default) to something else (e.g.'@'). This can be done on the Tools -> Tool Settings dialog.

    Andy

  9. #9
    Join Date
    Jun 2016
    Posts
    1

    problem with semicolon in INSERT for DB2

    Thanks for your help. Using ||CHR(59)|| instead of ";", saved my life...

  10. #10
    Join Date
    Jun 2016
    Location
    Toronto, ON, Canada
    Posts
    2

    Red face Problem solved

    Quote Originally Posted by reddevil View Post
    Hey Everyone,
    Ive finally solved my own problem. Just thought id post it to let others know. Turned out to be really simple!!

    e.g.
    insert into "~TMPCLP361431" values ('document 1','bla bla bla; bla bla bla');
    in the command center stops at the ";"

    instead use:
    insert into "~TMPCLP361431" values ('document 1','bla bla bla'||CHR(59) ||'bla bla bla');

    Thanks for your help Sam, its much appreciated.
    Awesome! I had the same question. Glad you asked it. Thanks, guys!

Posting Permissions

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