Results 1 to 5 of 5

Thread: SQL syntax 10g

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: SQL syntax 10g

    Hi guys

    Need some advice on SQL syntax.

    I need to update a column containing a string value which contains the following string as part of it.

    e.g., Full string is:

    Code:
    select datetime, col1, col2, SUBSTR(OBJECT_,INSTR(OBJECT_,'=')+1,6) col3
    Now I need to replace the following part of the string mentioned above

    Code:
    SUBSTR(OBJECT_,INSTR(OBJECT_,'=')+1,6)
    with

    Code:
    SUBSTR(OBJECT_,INSTR(OBJECT_,'=',1,1)+1,INSTR(OBJECT_,',',1,1)-INSTR(OBJECT_,'=',1,1)-1)
    so I said:

    Code:
    update schema.table set column_name=REPLACE ('SUBSTR(OBJECT_,INSTR(OBJECT_,''='')+1,6)','SUBSTR(OBJECT_,INSTR(OBJECT_,''='',1,1)+1,INSTR(OBJECT_,'','',1,1)-INSTR(OBJECT_,''='',1,1)-1)')
    but it didn't work. 0 rows were updated.

    Please advise?

    Regards
    Shajju
    Last edited by shajju; 03-15-13 at 14:58.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How about posting a test case? You know, CREATE TABLE and INSERT INTO several sample records so that we'd have "input". Then, describe what you want to do with it and show us what "output" you desire.

    UPDATE didn't do anything because part on the right side of the equation sign is wrong.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see the syntax of REPLACE function.
    REPLACE

    REPLACE( char , search_string [, replacement_string ])


    Thogh I have some doubt on your update statement(e.g. no WHERE clause),
    if your "update schema.table ..." statement might go to a right direction,
    please try something like...
    Code:
    UPDATE schema.table
     SET   column_name
         = REPLACE(
              column_name
            , 'SUBSTR(OBJECT_,INSTR(OBJECT_,''='')+1,6)'
            , 'SUBSTR(OBJECT_,INSTR(OBJECT_,''='',1,1)+1,INSTR(OBJECT_,'','',1,1)-INSTR(OBJECT_,''='',1,1)-1)'
           )
    ;
    Last edited by tonkuma; 03-15-13 at 17:52.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by shajju View Post
    ...
    ...

    Now I need to replace the following part of the string mentioned above

    Code:
    SUBSTR(OBJECT_,INSTR(OBJECT_,'=')+1,6)
    with

    Code:
    SUBSTR(OBJECT_,INSTR(OBJECT_,'=',1,1)+1,INSTR(OBJECT_,',',1,1)-INSTR(OBJECT_,'=',1,1)-1)
    ...
    Why did you changed to "INSTR(OBJECT_,'=',1,1)"?
    Before replacement, you used
    Code:
    SUBSTR(OBJECT_,INSTR(OBJECT_,'=')+1,6)

    How about to replace the string by this?
    Code:
    SUBSTR(OBJECT_,INSTR(OBJECT_,'=')+1,INSTR(OBJECT_,',')-INSTR(OBJECT_,'=')-1)

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Thanks for the input 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
  •