0

My PostgreSQL query returns no results when a search string starts with an German umlaut like Ö. I am using ilike to get my results.

The problem is if only the first letter is an umlaut.... The PostgreSQL query alone works fine. There seems to be the problem with the combination of grails. Making further research I found out there is a problem with ilike when I change it to like it works fine.

def searchResult = Termin.createCriteria().listDistinct{

                    or  {                       
                          ilike('vorname', '%' + searchString.trim() + '%')
                          ilike('nachname', '%' + searchString.trim() + '%')
                    }

                    order('nachname', 'asc')
                    maxResults(27) 
                }
Chris Travers
  • 25,424
  • 6
  • 65
  • 182
Jacob
  • 3,580
  • 22
  • 82
  • 146
  • 1
    I cannot reproduce this. [Check this SQLFiddle](http://sqlfiddle.com/#!1/2eb08/1) – DrColossos May 14 '13 at 09:12
  • What's your PostgreSQL version? If it's 8.4 or older, upgrade, unicode pattern matching was improved greatly in newer versions. Otherwise, please show your locale and charset; see `\l+` in `psql` – Craig Ringer May 14 '13 at 09:14
  • I have a version 9.1. I am using grails as my framework maybe the problem is related to that. – Jacob May 14 '13 at 09:23
  • 1
    Regular expressions in Postgres accept accentuated characters. So `field ~ '^Ö'` will work. – greg May 14 '13 at 09:37
  • The condition content is from a search field so it is dynamically created... – Jacob May 14 '13 at 09:58
  • Can you enable sql output to see what Hibernate is generating? http://stackoverflow.com/questions/2568507/how-to-log-sql-statements-in-grails –  May 16 '13 at 21:10
  • I've seen a number of cases in Python where the problem was due to character encoding issues in the Python rather than the underlying PostgreSQL database, although it's not a bad idea to make sure the underlying tables are in UTF-8 encoding (the default for some years now). – Alex North-Keys May 17 '13 at 06:08

0 Answers0