PATINDEX IN HIBERNATE?

edward · **Posted:** Thu Jun 22, 2006 3:46 am

Hi

Is any Function PATINDEX Available in Hibernate? or how can find the index of searching query using HIBERNATE QUERY?

Thanks
edward

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

It's not in any of the dialects yet. You can either use SQL query to make use of it, or edit your dialect file and rebuild your hibernate jar. Those files are in org/hiberate/dialect, just add a line like

Code:

registerFunction( "patindex", new StandardSQLFunction("patindex") );

to the constructor of the dialect you use.

There are other string-searching methods provided. Have a look in the ref docs section 14.9, "Expressions", for a list.

edward · **Posted:** Fri Jun 23, 2006 12:55 am

Excuse me,

Could You explain step by step. It's very much helpful for me. Because I am a beginner of Hibernate

Thanks

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You have two options. The first is to write your query as an SQL query instead of an HQL query.

Code:

Query q = session.createSQLQuery("select patindex('%yourstring%', YourColumn) from YourTable");

The second is to download the source code for Hibernate, edit the appropriate Dialect file (as pointed to by the hibernate.dialect property in your hibernate.cfg.xml file), add the registerFunction line I posted earlier, and rebuild the hibernate jar. For example, if your hibernate.cfg.xml file includes this line:

Code:

  <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

Then you'll edit org/hibernate/dialect/MySQLDialect.java.

edward · **Posted:** Fri Jun 23, 2006 7:47 am

Hi, It's working fine Now.

I have one more doubt.

My database contain xml format data like

1 <ISSUELIST data_1="23" data_34="34">
2 <ISSUELIST data_1="20" data_34="34">

I want to fetch data data_1

my expected result is
===============
1 data_1 23
2 data_1 20
===============

Using patindex I have found the position.

How can i fetch the value of data_1 using Hibernate query.

Thanks
edward