2

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.SYSDUMMY1
Fidel
  • 7,027
  • 11
  • 57
  • 81

1 Answers1

3

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');

create function replace (sourceString varchar(8000), searchString varchar(8000), replaceString varchar(8000))
returns varchar(8000)
parameter style java no sql
language 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

Fidel
  • 7,027
  • 11
  • 57
  • 81
  • 1
    And the official docs list the [built-in SQL functions](https://db.apache.org/derby/docs/10.8/ref/rrefsqlj29026.html) (version 8.10) – hc_dev May 03 '21 at 19:45