1

We have some simple domain objects that we are doing like statements on. The data in the table often has underscores in this. The problem is, underscore is a wildcard character in oracle when used in a like statement. So far this is the closes piece I've found, but it doesn't seem to work quite right.

MyDomain{
def columnOne
def columnTwo
}

def results = myDomain.createCriteria().list(max:10, offset:10){
ilike("columnTwo","%BOB\_%")
sqlRestriction("ESCAPE \\")
}

The SQL that is being executed is

select count(*) as y0_ from MY_SCHEMA.MY_DOMAIN this_ where
lower(this_.COLUMN_TWO) like ?
and ESCAPE '\'

It appears that the sqlRestriction clause is being tagged on as an AND in the query. Is there a way to avoid this or is there a better way to format the criteria?

Joseph
  • 1,442
  • 21
  • 44

1 Answers1

2

One way is to use the like as sqlRestriction and add the escape in the string as you can see here.

def results = myDomain.createCriteria().list(max:10, offset:10){
  sqlRestriction("lower(column_two) like '%bob\_%' ESCAPE '\\' ")
}
Community
  • 1
  • 1