Replace by position


Have you ever wanted to replace a character in a string just by position? I needed this the other day and IMHO there is no way in Oracle PL/SQL to do this, so I decided to write this my self.

It is really simple code and it probably needs improvement but it works for now and I think it is rather simple. Any improvement suggestions are of course very welcome in the comments.

( source_in      IN VARCHAR2
  , replacechar_in IN VARCHAR2
  , position_in    IN NUMBER) RETURN VARCHAR2 IS
  l_returnvalue VARCHAR2(32767);
  — copy from the source string up to, but not including,
  — the character position
  — to be replaced
  l_returnvalue := substr( str1 => source_in
                         , pos => 1
                         , len => position_in – 1);
  — add the replacement character
  — just a single character, but more can be sent in,
  — so substring the parameter
  l_returnvalue := l_returnvalue ||
                    substr( str1 => replacechar_in
                          , pos =>
                          , len => 1);
  — copy the rest of the source string
  l_returnvalue := l_returnvalue ||
                    substr( str1 => source_in
                          , pos => position_in + 1);
  RETURN l_returnvalue;
END replacepos;

About Author

Patrick Barel is a PL/SQL Developer for AMIS Services in the Netherlands. Besides working with SQL and PL/SQL did he co-develop CodeGen and wrote different plug-ins for PL/SQL Developer. He publishes articles on the AMIS Technology blog and on his own blog


  1. Patrick Barel on

    Like I already replied to on Laurent Schneiders comment on the crossposting on
    Which is ofcourse a valid way to do it. I definitely need to learn more about these regular expressions.

  2. Anton Scheffer on

    select regexp_replace( ‘abab’, ‘.’, ‘x’, 1, 3 )
    from dual replaces the character on position 3