Search words with accents / special chars

dolmo · **Joined:** Fri Jan 26, 2007 5:43 am **Posts:** 2

I'm using Hibernate 3.1 and i would like to know if there's a way to search words with accents without introducing them.
Let me explain with an example:

- In DB I have a field "name" with the value "Ramón"
- I would like to create a Criteria which ignore the accents and allow to search with Ramon and obtain the field Ramón

Thanks in advance.

David.

i92absaj · **Posted:** Fri Jan 26, 2007 8:12 am

Hello

I don't know how DB works. I used to work with MySql and using it I have no accent problem. In any way, you could use a where clause like that:

Code:

... where name in ('Ramon', 'Ramón', Rámon', 'Rámón');

I know it's no a elegant way, even I don't know if you could use IN option...
You should treat the input string: Ramon for obtaining the others strings Ramón, Rámon and so on.

Code:

Vector<String> names=new Vector<String>();
names = functionGetNames('Ramon') // it should return 'ramón', 'ramon'....
criterio.add(Restrictions.in("name", conjunto));

dolmo · **Joined:** Fri Jan 26, 2007 5:43 am **Posts:** 2

Thanks for the reply but this solution can not be used because I need to perform this kind of search for all posible words (not just Ramón), so doing in that way will decrease the performance of the system.

I was looking for an API method like ignoreCase() (which allows you to perform a search ignoring case for all string-valued properties), but i'm afraid it doesn't exists :-(

Any other idea?

christian · **Posted:** Fri Jan 26, 2007 8:43 am

You need something like a SQL soundex() function...

i92absaj · **Posted:** Fri Jan 26, 2007 9:09 am

First of all, sorry about my ignorance about DB.

Can you use a REPLACE function?

Code:

... where REPLACE( REPLACE( REPLACE( REPLACE( REPLACE ( LOWER(name),'á','a'), 'é','e'),'í','i'),'ó','o'),'ú','u') = 'ramon'

The idea it's to change every accented vowel to a non-accented one.

You are rigth about the like options...
options=2^(number of vowels)

janegres · **Joined:** Wed Jun 20, 2007 5:31 pm **Posts:** 1

Hi to All!!

In SQL Server 2005 you can do this

Code:

SELECT * 
   FROM CaseCheck.Names  
      WHERE Name like '%árbol%'  
COLLATE SQL_Latin1_General_CP1_CI_AI

With the collate you can say to SQL that does the comparisons
insesitive including the accents.

so for the query above "arbol" and "árbol" are the same word.

Now my question.

Can we Put a COLLATE in a HQL's ???

Theres is a way to enforce Hibernate do the comparisons with the COLLATE
that we say ??

Thanks in Advance.

vfuchs · **Joined:** Fri Jan 05, 2007 1:47 pm **Posts:** 7

Hi all,

Has anybody found a solution for that ? I would love to have that possibility, since I have names from all over the world in my DB, and when performing a search, users may not even have the accentuated character on their keyboard. The only solution I see so far is to create a kind of technical table which will contain all the names I'm suppose to search, with all accentuated characters replaced by their non accentuated version, and perform the search on that. Not very handy though....

Maybe a JIRA issue should be created ?

Thanks in advance

--
Vincent FUCHS

luismpcosta · **Joined:** Wed May 11, 2011 10:48 am **Posts:** 1

Anybody found a solution for that?

dutoitc01 · **Joined:** Wed Nov 13, 2013 7:52 am **Posts:** 1

My Solution for this:
__________________________

Code:

package my.app;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.criterion.CriteriaQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.hibernate.dialect.Dialect;
import org.hibernate.engine.spi.TypedValue;

/**
 * A criterion representing a "like" expression without diacritic signs.
 *
 * @author xsicdt based on LikeExpression from Hibernate
 */
public class LikeDiacriticlessExpression implements Criterion {
   private static final long serialVersionUID = 1L;
   private final String propertyName;
   private final Object value;
   private final Character escapeChar;
   private final boolean ignoreCase;
   private final String TRANSLATION = "'àâäçéèëêùûüôöïî', 'aaaceeeeuuuooii'";

   public LikeDiacriticlessExpression(
         String propertyName,
         String value,
         Character escapeChar,
         boolean ignoreCase) {
      this.propertyName = propertyName;
      this.value = value;
      this.escapeChar = escapeChar;
      this.ignoreCase = ignoreCase;
   }

   public LikeDiacriticlessExpression(
         String propertyName,
         String value) {
      this( propertyName, value, null, false );
   }

   public LikeDiacriticlessExpression(
         String propertyName,
         String value,
         MatchMode matchMode) {
      this( propertyName, matchMode.toMatchString( value ) );
   }

   public LikeDiacriticlessExpression(
         String propertyName,
         String value,
         MatchMode matchMode,
         Character escapeChar,
         boolean ignoreCase) {
      this( propertyName, matchMode.toMatchString( value ), escapeChar, ignoreCase );
   }

   public String toSqlString(
         Criteria criteria,
         CriteriaQuery criteriaQuery) throws HibernateException {
      Dialect dialect = criteriaQuery.getFactory().getDialect();
      String[] columns = criteriaQuery.findColumns(propertyName, criteria);
      if ( columns.length != 1 ) {
         throw new HibernateException( "Like may only be used with single-column properties" );
      }
      String escape = escapeChar == null ? "" : " escape \'" + escapeChar + "\'";
      String column = columns[0];
      
      
      StringBuffer sb = new StringBuffer();
      sb.append("translate(");
      sb.append(dialect.getLowercaseFunction());
      sb.append('(');
      sb.append(column);
      sb.append("),");
      sb.append(TRANSLATION);
      sb.append(") like translate(");
      sb.append(dialect.getLowercaseFunction());
      sb.append("(?),");
      sb.append(TRANSLATION);
      sb.append(')');
      sb.append(escape);
      return sb.toString();
   }

   public TypedValue[] getTypedValues(
         Criteria criteria,
         CriteriaQuery criteriaQuery) throws HibernateException {
      return new TypedValue[] {
            criteriaQuery.getTypedValue( criteria, propertyName, ignoreCase ? value.toString().toLowerCase() : value.toString() )
      };
   }
}

_________________________________
Use this as follow:

Code:

criteria.add(new LikeDiacriticlessExpression(field, query, MatchMode.EXACT, null, true));

Please note that translate() could not exist on all databases.