[EMAIL PROTECTED] wrote:
Hello,
First I apologize for my poor english...
No need, it's fine. :)
I'm looking for a simple way to implement a character replacement function.
I'm trying to do an application migration from WSAD/DB2 to Tomcat/Derby but
there are a lot of SQL queries which use the REPLACE(SOURCE STRING, OLD
CHARACTER, NEW CHARACTER) function. It seems that this function is not
built into Derby.
Do you have an idea?
Derby supports user-defined functions, which can be any (static)
function you can access from java. I don't even think you need to write
the function yourself, you can simply use java.lang.String's replace()
method. Try something like this (I haven't tested it myself):
Create the static java function:
public static String replace(String str, char old, char new)
{
return str.replace(old, new);
}
Create a SQL function using this statement:
CREATE FUNCTION REPLACE(STR VARCHAR, OLD CHAR(1), NEW CHAR(1)) RETURNS
VARCHAR PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME
'<your.class.Name>.replace'
I'm unsure about the data types in the definition of REPLACE, perhaps
what I've written doesn't work. It a) has to be correct syntax and b)
must map to the correct java types in your function. I haven't done this
with String/char parameters before (only int/INTEGER), so that's why I'm
unsure of this. Perhaps someone else can shine some light on this.
Otherwise, consult the manuals - it's not easy to find, but it's there
(somewhere).
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
