Changing Oracle String Delimiter
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Changing Oracle String Delimiter

Hybrid View

  1. #1
    Join Date
    Nov 2004
    Posts
    24

    Changing Oracle String Delimiter

    The default delimiter in Oracle is a quote or apostrophe ' , What I would like to do is change the delimiter in the session. Is there anyway to do this with Alter session or a SQL PLUS command where I can change the delimiter such as the SET command. I looked through the SET Command and I see things such as cmdsep and colsep, but nothing for delimiting or encapsulating strings.

    What I need to do is insert a character value that has apostrohpes, so before I insert I want to change the delimiter to something else and then do an insert such as [this is the string values that don't go ' ' ' '' ''' string etc... ] . So that it will insert everything between that and ignore ' as the seperator??

    Thanks

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Escape?
    I remember when this place was cool.

  3. #3
    Join Date
    Nov 2004
    Posts
    24

    Nope

    What do you mean by escape? I know in SQL Server you can do it.

    Im just thinking I need to use something other than SQLPLUS to do this. I wanted to write a script file.. looks like I might need to use sqlldr instead.

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: Changing Oracle String Delimiter

    Originally posted by djtomr941
    The default delimiter in Oracle is a quote or apostrophe ' , What I would like to do is change the delimiter in the session. Is there anyway to do this with Alter session or a SQL PLUS command where I can change the delimiter such as the SET command. I looked through the SET Command and I see things such as cmdsep and colsep, but nothing for delimiting or encapsulating strings.

    What I need to do is insert a character value that has apostrohpes, so before I insert I want to change the delimiter to something else and then do an insert such as [this is the string values that don't go ' ' ' '' ''' string etc... ] . So that it will insert everything between that and ignore ' as the seperator??

    Thanks
    what db version? does not 10g hace some funk feature to handle this?
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Nov 2004
    Posts
    24

    9i

    Oracle 9i, but heck I wold use 10g if it had this functionality.

  6. #6
    Join Date
    Nov 2004
    Posts
    24

    Found it

    Found it, don't know if this applies to 8i but it works on 9i and 10g.

    http://download-west.oracle.com/docs....htm#sthref379

    Basically precede your string with a q' and any character for example q'* so to get out of the string you would have to issue *'

    Or q'< Don't >' returns Don't , q'$ '$ etc.. you get the idea.

Posting Permissions

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


Click Here to Expand Forum to Full Width