SQL case insensitive search using jcr:path

[Blanco Emanuele <e....@reply.it>]

Hi,

I'm trying to write some SQL queries to search nodes in my Jackrabbit repository. I need to find nodes, given the node name, under a specified path.

I used something like this (assuming I need to search for a node named 'foobar' in descendants of /foo/bar):

SELECT * FROM ns:type WHERE jcr:path LIKE '/foo/bar/%/foobar'

However, if the node is stored as FOOBAR, Foobar, fooBar or so on, searching will not return any result. Is there a way to make this search case insensitive?

And, last but not least, there is a way to specify a "soundslike" concept, always using jcr:path?

Thank you.

--
Emanuele Blanco

________________________________
--
The information transmitted is intended for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

Re: SQL case insensitive search using jcr:path

[Marcel Reutegger <ma...@gmx.net>]

Hi Emanuele,

Blanco Emanuele wrote:
> However, if the node is stored as FOOBAR, Foobar, fooBar or so on, searching
> will not return any result. Is there a way to make this search case
> insensitive?

no, that's not possible. as a workaround you can set a string property on the
node, which contains the name. then you can write:

select * from ns:type where jcr:path like '/foo/bar/%' and upper(ns:name) = 'FOOBAR'

> And, last but not least, there is a way to specify a "soundslike" concept,
> always using jcr:path?

this also requires that the node name is available as a property. then you can
write:

select * from ns:type where jcr:path like '/foo/bar/%' and contains(ns:name,
'foobar~')


regards
 marcel