Replace by position

2

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.

CREATE OR REPLACE FUNCTION replacepos
 
( source_in      IN VARCHAR2
  , replacechar_in IN VARCHAR2
  , position_in    IN NUMBER) RETURN VARCHAR2 IS
  l_returnvalue VARCHAR2(32767);
BEGIN
  — 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 =>
1
                          , 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;

Share.

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 http://blog.bar-solutions.com/.

2 Comments

  1. Anton Scheffer on

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

Leave a Reply