You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by ce...@mpsa.com on 2005/11/02 11:07:59 UTC
Characters replacement function
Hello,
First I apologize for my poor english...
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?
Best regards
Cédric
Réf. : Re: Characters replacement function
Posted by 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/
Re: Characters replacement function
Posted by Oy...@Sun.COM.
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/
Re: Characters replacement function
Posted by Fernanda Pizzorno <Fe...@Sun.COM>.
Oyvind.Bakksjo@Sun.COM wrote:
> Fernanda Pizzorno wrote:
>
>> Hello,
>>
>> Have you tried to create a user defined function for replace? You can
>> do that using the CREATE FUNCTION statement
>> (http://db.apache.org/derby/docs/10.1/ref/rrefcreatefunctionstatement.html).
>> I have tried creating a very simple java method that does the replace
>> and it seems to work fine.
>>
>> Here is what I tried:
>>
>> 1. Java method
>> public static String replace (String orgStr, String oldStr, String
>> newStr) {
>> return orgStr.replace(oldStr, newStr);
>> }
>>
>> 2. User defined function
>> CREATE FUNCTION REPLACE(orgStr VARCHAR(50), oldStr VARCHAR(50),
>> newStr VARCHAR(50)) RETURNS VARCHAR(50)
>> PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
>> EXTERNAL NAME 'StringReplaceTest.replace';
>>
>> 3. Test
>> ij> values replace('fernanda', 'a', 'e');
>> 1
>>
>> --------------------------------------------------------------------------------------------------------------------------------
>>
>> fernende
>
>
> What happens if you execute "values replace('banana', 'an', 'ul')"?
> How does VARCHAR(50) map to a java char?
>
That's what happens:
ij> values replace('banana', 'an', 'ul');
1
--------------------------------------------------------------------------------------------------------------------------------
bulula
1 row selected
Why should it map to java char?
My intention was use java.lang.String.replace(CharSequence,
CharSequence) (since 1.5), it would then work with a string of 1 or more
characters. If instead you want to use java.lang.String.replace(char,
char), I guess you could just replace strings by chars, and varchars by
chars where it is needed and it would work, but I have not tried it.
Best regards,
Fernanda
Re: Characters replacement function
Posted by Oy...@Sun.COM.
Fernanda Pizzorno wrote:
> Hello,
>
> Have you tried to create a user defined function for replace? You can do
> that using the CREATE FUNCTION statement
> (http://db.apache.org/derby/docs/10.1/ref/rrefcreatefunctionstatement.html).
> I have tried creating a very simple java method that does the replace
> and it seems to work fine.
>
> Here is what I tried:
>
> 1. Java method
> public static String replace (String orgStr, String oldStr, String
> newStr) {
> return orgStr.replace(oldStr, newStr);
> }
>
> 2. User defined function
> CREATE FUNCTION REPLACE(orgStr VARCHAR(50), oldStr VARCHAR(50),
> newStr VARCHAR(50)) RETURNS VARCHAR(50)
> PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
> EXTERNAL NAME 'StringReplaceTest.replace';
>
> 3. Test
> ij> values replace('fernanda', 'a', 'e');
> 1
>
> --------------------------------------------------------------------------------------------------------------------------------
>
> fernende
What happens if you execute "values replace('banana', 'an', 'ul')"? How
does VARCHAR(50) map to a java char?
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: Characters replacement function
Posted by Fernanda Pizzorno <Fe...@Sun.COM>.
Hello,
Have you tried to create a user defined function for replace? You can do
that using the CREATE FUNCTION statement
(http://db.apache.org/derby/docs/10.1/ref/rrefcreatefunctionstatement.html).
I have tried creating a very simple java method that does the replace
and it seems to work fine.
Here is what I tried:
1. Java method
public static String replace (String orgStr, String oldStr, String
newStr) {
return orgStr.replace(oldStr, newStr);
}
2. User defined function
CREATE FUNCTION REPLACE(orgStr VARCHAR(50), oldStr VARCHAR(50),
newStr VARCHAR(50)) RETURNS VARCHAR(50)
PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA
EXTERNAL NAME 'StringReplaceTest.replace';
3. Test
ij> values replace('fernanda', 'a', 'e');
1
--------------------------------------------------------------------------------------------------------------------------------
fernende
1 row selected
Best regards,
Fernanda
cedric.maucourt1@mpsa.com wrote:
>
>
>Hello,
>
>First I apologize for my poor english...
>
>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?
>
>Best regards
>
>Cédric
>
>
>