Replace by position

Patrick Barel 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;

2 thoughts on “Replace by position

  1. Like I already replied to on Laurent Schneiders comment on the crossposting on http://blog.bar-solutions.com:
    Which is ofcourse a valid way to do it. I definitely need to learn more about these regular expressions.

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

Comments are closed.

Next Post

ADF11gR2: Plotting custom locations on a Thematic Map using a GeoCoder Service

After my initial adventures (see https://technology.amis.nl/blog/12198/first-impressions-of-the-thematic-map-component-in-jdeveloper-11gr2) I continue to explore the wonderful world of the Thematic Map component. The previous article demonstrated how data in database tables could be turned into plotted cities on a Thematic Map. This made use of the preshipped maps and the preshipped list of cities […]
%d bloggers like this: