Skip to Main Content

Database Software

Replace text at a particular location/offset

Rafiq DApr 21 2016

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

Comments
Post Details
Added on Apr 21 2016
2 comments
16,728 views