Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Wed, 02 Nov 2005 12:41:06 +0100
From: Fernanda Pizzorno <Fernanda.Pizzorno@Sun.COM>
Subject: Re: Characters replacement function
In-reply-to: 
 <OF4107C173.30CEC5A2-ONC12570AD.0036EC54-C12570AD.0037A9B7@mpsa.com>
To: derby-dev@db.apache.org
Message-id: <4368A5D2.7080605@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 8BIT
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050930)
References: 
 <OF4107C173.30CEC5A2-ONC12570AD.0036EC54-C12570AD.0037A9B7@mpsa.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
>
>  
>