Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    17

    Unanswered: Escaping characters in INSERT

    Hello,

    I'm trying to insert the following values into a table, with this query:

    INSERT INTO alias (aliasid, objid, aliastext) VALUES (19, 367, 'LF I&N-GSJ');

    The problem is, as you may have guessed, the '&' character.

    Following the docs, I used both braces {} and backslash \ to escape the whole string, or just the & character, but I couldn do it:


    SQL> insert into alias (aliasid, objid, aliastext) values (19, 367, '{LF I&N-GSJ}');
    Intoduce a value for n:
    old 1: insert into alias (aliasid, objid, aliastext) values (19, 367, '{LF I&N-GSJ}')
    new 1: insert into alias (aliasid, objid, aliastext) values (19, 367, '{LF I-GSJ}')

    1 fila creada.

    This statement removes the &N from the string. The same thing happens if I escape the & character with the backslash.

    Any ideas?

    Thanks in advance

    Miguel Marzano

  2. #2
    Join Date
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    try in sql*plus
    SQL> set escape on
    SQL> insert into alias (aliasid, objid, aliastext) values (19, 367, 'LF I\&N-GSJ');

    you can use any other character c instead of backslash with
    SQL> set escape c

    Another solution without set ecape : use concatenation .

    SQL> insert into alias (aliasid, objid, aliastext) values (19, 367, 'LF I&'||'N-GSJ');

  3. #3
    Join Date
    Mar 2004
    Posts
    17
    Thanks lyonnais, that did it!

    Seems that ESCAPE was off, silly of me.

  4. #4
    Join Date
    Mar 2004
    Posts
    50
    Originally posted by mmarzano
    Thanks lyonnais, that did it!

    Seems that ESCAPE was off, silly of me.

    Another alternative is

    SQL > Set Define @

    This statement forces the SQL Plus to prompt for varibles when it encounters '@" sybol , instead of '&' symbol
    The symbol '@' can be replaced by any other symbol in the above command.

    Ishan.

  5. #5
    Join Date
    Mar 2004
    Posts
    17
    that's good to know. Thanks for the reply!

    Originally posted by ishanbansal
    Another alternative is

    SQL > Set Define @

    This statement forces the SQL Plus to prompt for varibles when it encounters '@" sybol , instead of '&' symbol
    The symbol '@' can be replaced by any other symbol in the above command.

    Ishan.

Posting Permissions

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