Replace by position

Patrick Barel 2
0 0
Read Time:1 Minute, 52 Second

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;

About Post Author

Patrick Barel

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/.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

2 thoughts on “Replace by position

  1. 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: