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