Regex in grails CreateCriteria

Sometimes we need to retrieve the result on the basis of some regular expression pattern which could be applied to a column. In such situations its better to use a rlike method provided by Grails CreateCriteria. Using rlike method it’s easy to retrieve Result based upon the Required Pattern.
rlike Method is Similar to like, but uses a regex. Only supported on Oracle and MySQL.

Syntax: rlike(“columnName”,/Regular Expression/)
e.g.:
rlike(“holderFirstName”,/Steph.+/)

.     :  Checks the matched expression for 0 or 1 Characters/Numerics
+    :  Checks the matched expression for 1 or More Characters/Numerics
*     :  Checks the matched expression for 0 or More Characters/Numerics
[0-9]: Checks the matched expression for 1 or more Numeric(Between 0 to 9)
[a-z]: Checks the matched expression for 1 or more Character(Between a to z or A to Z)
^     :    Checks that the matched expression should start with the letters/numbers following it 
$     :    Checks that the matched expression should end with the letters/numbers before it

Example1:

def userList=User.createCriteria().list{
        projections {
                property("firstName")
                      }
      //check for pattern that start with F and has 5 letters in it and ends with digit
     rlike("firstName",/^F.....[0-9]$/)
 }
Output: [First10, First11]

Example2:If we replace rlike in Example1 with this:

 //check for pattern that start with F and has 0 or more letters in it and ends with digit
rlike("firstName",/^F.*[0-9]$/)
Output:[First0, First1, First2, First3,First10, First11]

Example3:If we replace rlike in Example1 with this:

 //check for pattern that start with L and has 0 or more letters in it and ends with digit
rlike("lastName",/^L*[0-9]$/)
Output: []

This Expression won’t works. It will return an Empty List.

Example4:If we replace rlike in Example1 with this:

 //check for pattern that start with First and has 1 or more letters in it
rlike("firstName",/First.+/)
Output:[First0, First1, First2, First3,First10, First11]

Example5:

def  userList=User.createCriteria().list{
        projections {
                property("firstName")
                property("lastName")
         }
  rlike("lastName",/^L.*[0-9]$/)       //  rlike("lastName",/^L.*[0123456789]$/)
  }
Output:[[First0, Last 0], [First1, Last 1], [First2, Last 2], [First3, Last 3],
[First10, Last 10], [First11, Last 11]]

For more information you can visit this site:
http://www.grails.org/doc/latest/ref/Domain%20Classes/createCriteria.html

Hope it Helps!!!

Regards,
Gautam Malhotra
Intelligrape Software
http://www.tothenew.com/blog/