Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (athena.apache.org: local policy)
From: Henno Vermeulen <henno@huizemolenaar.nl>
To: "'users@openjpa.apache.org'" <users@openjpa.apache.org>
Date: Mon, 9 May 2011 17:58:02 +0200
Subject: query that uses String REPLACE function
Thread-Topic: query that uses String REPLACE function
Thread-Index: AcwOYGMgzPdVgQSNRDyM37CSjpLa8w==
Message-ID: <1C448C478A6B4743AF19DBC3C3DCE13201BD1D83CC40@HMS.hm.local>
Accept-Language: nl-NL
Content-Language: nl-NL
acceptlanguage: nl-NL
Content-Type: multipart/alternative;
	boundary="_000_1C448C478A6B4743AF19DBC3C3DCE13201BD1D83CC40HMShmlocal_"
MIME-Version: 1.0

--_000_1C448C478A6B4743AF19DBC3C3DCE13201BD1D83CC40HMShmlocal_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have a Contact entity with a telephone number field of type String. I wis=
h to query for Contacts that have a given telephone number. For this compar=
ison to succeed I have to replace some characters in the stored field ("+" =
by 00 and "-" and space by an empty string). With a sql server native query=
 I can do something like this:

SELECT * FROM Contact WHERE
REPLACE(REPLACE(REPLACE(telephone,' ',''), '+', 00), '-', '') LIKE @phoneNu=
mber

What would be my best option to do this with JPA?

I was thinking of one of these possibilities:


-          Criteria API. Put the three REPLACE's in a user defined function=
. If this is possible, call this UDF with the criteria API.

-          Use a native query

-          Map an entity to a database view that has a telephone column wit=
h the characters replaced

-          Always store the telephone number with the characters already re=
placed

But the first three feel much too complicated for such a simple problem. Th=
e last one is a bit too strict for my taste but I think it is the best opti=
on unless anyone knows a better solution that I can use.

Regards,
Henno Vermeulen
Huize Molenaar

--_000_1C448C478A6B4743AF19DBC3C3DCE13201BD1D83CC40HMShmlocal_--