db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oyvind.Bakk...@Sun.COM
Subject Re: Characters replacement function
Date Wed, 02 Nov 2005 13:17:02 GMT
cedric.maucourt1@mpsa.com 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/

Mime
View raw message