Replace String In Apache Derby

Does Apache Derby have a way of replacing a string with another string? I'm looking for something like: select replace('bruce bogtrotter', 'bruce', 'john') from SYSIBM.SYSDUMMY

Solution 1:

Apache Derby doesn't have a native function, but it's possible to create one using by using the Apache Commons Lang library.

Download the Lang library from here

call SQLJ.INSTALL_JAR('C:\path_to_libraries\commons-lang3-3.4.jar', 'APP.commons_lang', 0);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'APP.commons_lang');

createfunction replace (sourceString varchar(8000), searchString varchar(8000), replaceString varchar(8000))
returnsvarchar(8000)
parameter style java nosqllanguage java external name 'org.apache.commons.lang3.StringUtils.replace';

select
    replace('bruce bogtrotter', 'bruce', 'john')
from SYSIBM.SYSDUMMY1

There are a number of other useful functions here