Réf. : Re: Characters replacement function

[ce...@mpsa.com]

Thanks a lot, it works fine!
(with some modifications to the data types in the definition of the SQL
function and the corresponding Java function)

Here is the java function :
public final class StringUtils {

      private StringUtils(){

      }

      public static String replace(String str, String oldChar, String
newChar)
      {
          return str.replace(oldChar.charAt(0), newChar.charAt(0));
      }
}

And the SQL function declaration :
CREATE FUNCTION REPLACE(STR VARCHAR(50), OLD CHAR(1), NEW CHAR(1)) RETURNS
VARCHAR(50) PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME
'StringUtils.replace'

Best regards,

Cédric



                                                                           
             Oyvind.Bakksjo@S                                              
             un.COM                                                        
                                                                      Pour 
             02/11/2005 14:17          derby-dev@db.apache.org             
                                                                        cc 
                                                                           
                 Veuillez                                            Objet 
                répondre à             Re: Characters replacement function 
             derby-dev@db.apa                                              
                 che.org                                                   
                                                                           
                                                                           
                                                                           
                                                                           




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/