1

I've the sample input as "Mickaël"
When I hit the database, to retrive values by adding criteria in hibernate code snippet is as follows. 

pCriteria.add(Restrictions.ilike("lastName", lLastName.toLowerCase() + "%"));

I get the result only with "mickaël".

But as of my Requirement I need to fetch both "Mickaël" and "Mickael"

can somebody help out with this??? TIA

RaMs_YearnsToLearn
  • 459
  • 6
  • 11
  • It seems that you have a problem with Collation. Check if you can modify the collation for the corresponding column. – Arnaud Denoyelle Jul 17 '14 at 14:52
  • @ArnaudDenoyelle I'm new to this terms of collation. can you give me link to any site or tell me briefly what it is. Bdw thnx for the early reply – RaMs_YearnsToLearn Jul 17 '14 at 14:53
  • Collation is the thing that makes e=E=é=ë. For example, the most known collation is "ci : character insensitive" : e = E != é. Take a look at http://dev.mysql.com/doc/refman/5.0/fr/charset-collate.html for example. – Arnaud Denoyelle Jul 17 '14 at 14:55
  • thnx for the link @ArnaudDenoyelle going thru it. – RaMs_YearnsToLearn Jul 17 '14 at 14:58
  • I gave you a link in French, sorry. Here is a list of possibles collations (in English :) ) : http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html – Arnaud Denoyelle Jul 17 '14 at 15:08

2 Answers2

1

You have to set a collation for example COLLATE French_CI_AI, either on the table or the query (see this post)

Community
  • 1
  • 1
alain.janinm
  • 19,951
  • 10
  • 65
  • 112
  • Thanks for the link and help. but the link says it is for case sensitive search, what if I need case insensitve?? any leads?? I tried.. I didn't find any. TIA – RaMs_YearnsToLearn Jul 18 '14 at 06:45
  • 1
    The link is just an example of how to set collation in Hibernate criteria. Don't use the collation from the example, but the one from my post (CI means case insensitive and AI accent insensitive) – alain.janinm Jul 18 '14 at 06:48
  • I used what you said. now my code to add criteria looks like this. `pCriteria.add(Restrictions.sqlRestriction("PER_VA_LAST_NAME = ? collate French_CI_AI",lLastName,new StringType()));` But it throws SQL exception (SQL command not properly ended). I want to ask if this 'French_CI_AI' is supported by Oracle database or only SQL Server??? If you'd need I'd like to provide the hibernate generated SQL query. – RaMs_YearnsToLearn Jul 18 '14 at 07:27
  • 1
    Sorry, didn't know you are using Oracle. It seems the syntax is different for Oracle. See this doc : http://docs.oracle.com/cd/E16655_01/server.121/e17750/ch5lingsort.htm#NLSPG0051. Also I'm not sure it makes any difference but it seems that they use collation in uppercase, try with FRENCH_CI_AI in NLS_SORT. Or use ALTER SESSION SET NLS_SORT=FRENCH_CI_AI; – alain.janinm Jul 18 '14 at 07:46
  • Thanx alain. Your leads helped me.. I'm able to retrive things and made work on Database. It's just how to do those things in HIBERNATE is bothering me. Thanks again – RaMs_YearnsToLearn Jul 18 '14 at 08:27
  • Actually I don't know how to do this with Hibernate. You can execute native or sql query: http://docs.jboss.org/hibernate/entitymanager/3.6/reference/en/html_single/. – alain.janinm Jul 18 '14 at 08:37
  • Try to run a native query or SQL query (don't know the difference) with the alter session statement. – alain.janinm Jul 18 '14 at 08:39
0

Try like this:

pCriteria.add(Restrictions.ilike("lastName", lLastName.toLowerCase().replaceAll('[ë]', '_')));

I am trying to make this string Micka_l. The sign '_' stands for any character, so if you have something like Micka^l or Mickaql in your table you will see this records too. You can add more special french characters in square brackets like this [ëöèù] to escape other special characters.

If you don't want to have other characters try this:

pCriteria.add(pCriteria.or(
    Restrictions.eq("lastName", lLastName.toLowerCase())), 
    Restrictions.eq("lastName", lLastName.toLowerCase().replaceAll('[ë]', 'e'))
  )
);

I assume that you input the whole word, if you don't change eq to ilike and add percent sign at the ends of the lines, like this:

pCriteria.add(pCriteria.or(
    Restrictions.ilike("lastName", lLastName.toLowerCase() + "%")), 
    Restrictions.ilike("lastName", lLastName.toLowerCase().replaceAll('[ë]', 'e') + "%")
  )
);

If there is another characters to escape you have to work around them like above.

neshkeev
  • 6,280
  • 3
  • 26
  • 47