Forum Stats

  • 3,874,727 Users
  • 2,266,768 Discussions
  • 7,911,961 Comments

Discussions

Replace text at a particular location/offset

Rafiq D
Rafiq D Member Posts: 57 Blue Ribbon
edited Apr 21, 2016 4:19PM in Database Ideas - Ideas

Hi Community,

I saw this code in the resource pack of a book that I'm reading (Oracle SOA Suite 12c Handbook by Lucas Jellema) and was wondering if Oracle has a better and simpler way of doing it:

 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;

Bottom line, the author wants to replace ANY character AT A CERTAIN POSITION in a string. For example,

SELECT replacepos('NNNNNNN','Y',5) FROM dual;

will return 'NNNNYNN'. As you will observe, the character at position 5 is replaced. In the function, this is achieved by breaking the string into three parts, replacing the second part and reassembling all the parts into a single string that is returned to the caller. I looked at the doc of the REPLACE function and realized it only searches for 'search_string' and replaces its occurrence with 'replacement_string' in the string that is passed in. In the above case however, the author doesn't care what character is present at a particular location; he wants to replace whatever character is at that location. So for example

SELECT replace('NHNMRDW','LO',5) FROM dual;

will return 'NHNMLODW'.

Overloading the REPLACE FUNCTION to do this will be awesome if there isn't an existing version. To expand its usefulness, a third parameter could be added with a default value of 1 to indicate how many characters from the specified position should be replaced. Thus in the example below,

SELECT REPLACE('NNNNNNN','Y',5,3) FROM dual;

the result will be 'NNNNY'. I believe this will be awesome...

dmak2709
6 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown
    edited Apr 22, 2016 6:34AM

    Regexp_replace is already able to do that using backreferences.

    However the syntax is more complex.

    Example

    SELECT regexp_replace('NNNNNNN','(^.{4})(.{1})(.*)$','\1Y\3') FROM dual;

    NNNNYNN

    SELECT regexp_replace('NHNMRDW','(^.{4})(.{1})(.*)$','\1LO\3') FROM dual; 

    NHNMLODW

    SELECT regexp_replace('NNNNNNN','(^.{4})(.{3})(.*)$','\1Y\3') FROM dual;

    NNNNY

    But since regular expressions are so much more powerful, I would prefer to learn and use them for complex search and replace operations, instead of learning another function.

  • Rafiq D
    Rafiq D Member Posts: 57 Blue Ribbon

    Regexp_replace is already able to do that using backreferences.

    However the syntax is more complex.

    Example

    SELECT regexp_replace('NNNNNNN','(^.{4})(.{1})(.*)$','\1Y\3') FROM dual;

    NNNNYNN

    SELECT regexp_replace('NHNMRDW','(^.{4})(.{1})(.*)$','\1LO\3') FROM dual; 

    NHNMLODW

    SELECT regexp_replace('NNNNNNN','(^.{4})(.{3})(.*)$','\1Y\3') FROM dual;

    NNNNY

    But since regular expressions are so much more powerful, I would prefer to learn and use them for complex search and replace operations, instead of learning another function.

    Thanks Sven for providing this example. I agree with you on that. There are already many functions and concepts. This wouldn't be necessary.