Alternative to LIKE Clause

[Rafael Afonso]

Hello,
I would like use clause LIKE in my EJB-QL query. Like this:

 SELECT OBJECT(sc) FROM StateCity sc WHERE sc.stateCityName LIKE ?1% 

. But it doesn�t works. Reading other posts and forums, I perceived that it is not possible use LIKE with parameter, only with constants.
So I tried other alternative. I wrote my query this:

SELECT OBJECT(sc) FROM StateCity sc WHERE SUBSTRING(sc.stateCityName, 0, LENGTH(?1)) = ?1 

. It works!
Now, I want your opinion. Is this a acceptable alternative? Or is this a ugly patch?
Thanks,

[Matthew Webster]

I have a...
<finder>
<name>findByStartsWithAndLocale</name>
<query>entry LIKE "{0}%" AND locale = {1}</query>
<order></order>
</finder>
block in my JAWS.xml. It works fine. Maybe it was the surrounding characters of your arg.
Good luck.

[Rafael Afonso]

Originally posted by Matthew Webster:
I have a...
<finder>
<name>findByStartsWithAndLocale</name>
<query>entry LIKE "{0}%" AND locale = {1}</query>
<order></order>
</finder>
block in my JAWS.xml.

Matthew:
I've never saw a query like yours. From I read We use "?1" for parameters not "{0}". Is this a Extension to EJB-QL of JBoss?
Thanks,

[rotterdam sam]

that is a brilliant solution! couldn't find it anywhere else. You are a very smart man. Thanks for sharing that!

[Paulo Pinto]

I'm using now JBoss 4.2.3.GA and that SUBSTRING doesn't seem to work for this purpose anymore.

But I tried this and it worked for me:

SELECT OBJECT(o) FROM Person o WHERE o.name LIKE CONCAT('%', ?1)

This simulates the query:

SELECT OBJECT(o) FROM Person o WHERE o.name LIKE %?1

You can even do a CONCAT(CONCAT('%', ?1), '%') if you need something like '%?1%' instead.