Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Posts
    6

    Unanswered: Update a specific character in a string.

    Hi All...

    I want to update a character in a specific position in a string. So I might have 'AAAAA' and I want to update the 3rd character to 'B' giving 'AABAA'

    On informix is trivial..

    * update table1 set col1[3]= 'B'

    But on Oralce.. it seems the only way is...

    * update table1 set col1 = substr(col1,1,2) || 'B' || substr(col1,4);

    So the question is... is this truely the only way I can do this Oracle? Or is ther some function I am not aware of that will help.

    Thanks....
    Roger

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Yes, that's the only way (and I don't know any RDBMS where this can be done differently)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Regular expressions:
    Code:
    SQL> select regexp_replace('AAAAA', '[[:alpha:]]', 'B', 3, 1) result
      2  from dual;
    
    RESUL
    -----
    AABAA
    
    SQL>

  4. #4
    Join Date
    Jul 2006
    Posts
    49
    Oracle supports regular expressions. select regexp_replace('AAAAA','*','B',3,1) from dual;

    The 'AAAAA' can be replaced by a column name, and dual can be replaced by your table name.

    The '*' is a pattern match, and I used an asterisk for all.

    The 'B' is what you want the new value to be.

    The 3 is the position to start checking. You said you wanted to check the 3rd position.

    The 1 is the occurrence. You only want to replace the 3rd character, not every 3rd character.

  5. #5
    Join Date
    Dec 2012
    Posts
    6
    Excellent, thanks for the responses, all very useful...

Posting Permissions

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