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.
\nrlike Method is Similar to like, but uses a regex. Only supported on Oracle and MySQL.<\/p>\n
Syntax: rlike(“columnName”,\/Regular Expression\/)
\ne.g.:
\nrlike(“holderFirstName”,\/Steph.+\/)<\/p>\n
\r\n. : Checks the matched expression for 0 or 1 Characters\/Numerics\r\n+ : Checks the matched expression for 1 or More Characters\/Numerics\r\n* : Checks the matched expression for 0 or More Characters\/Numerics\r\n[0-9]: Checks the matched expression for 1 or more Numeric(Between 0 to 9)\r\n[a-z]: Checks the matched expression for 1 or more Character(Between a to z or A to Z)\r\n^ : Checks that the matched expression should start with the letters\/numbers following it \r\n$ : Checks that the matched expression should end with the letters\/numbers before it\r\n<\/pre>\nExample1:<\/p>\n
\r\ndef userList=User.createCriteria().list{\r\n projections {\r\n property(\"firstName\")\r\n }\r\n \/\/check for pattern that start with F and has 5 letters in it and ends with digit\r\n rlike(\"firstName\",\/^F.....[0-9]$\/)\r\n }\r\nOutput: [First10, First11]\r\n<\/pre>\n\nExample2:If we replace rlike in Example1 with this:<\/p>\n
\r\n \/\/check for pattern that start with F and has 0 or more letters in it and ends with digit\r\nrlike(\"firstName\",\/^F.*[0-9]$\/)\r\nOutput:[First0, First1, First2, First3,First10, First11]\r\n<\/pre>\n\nExample3:If we replace rlike in Example1 with this:<\/p>\n
\r\n \/\/check for pattern that start with L and has 0 or more letters in it and ends with digit\r\nrlike(\"lastName\",\/^L*[0-9]$\/)\r\nOutput: []\r\n<\/pre>\nThis Expression won’t works. It will return an Empty List.
\n
\nExample4:If we replace rlike in Example1 with this:<\/p>\n
\r\n \/\/check for pattern that start with First and has 1 or more letters in it\r\nrlike(\"firstName\",\/First.+\/)\r\nOutput:[First0, First1, First2, First3,First10, First11]\r\n<\/pre>\n<\/p>\n
Example5:<\/p>\n
\r\ndef userList=User.createCriteria().list{\r\n projections {\r\n property(\"firstName\")\r\n property(\"lastName\")\r\n }\r\n rlike(\"lastName\",\/^L.*[0-9]$\/) \/\/ rlike(\"lastName\",\/^L.*[0123456789]$\/)\r\n }\r\nOutput:[[First0, Last 0], [First1, Last 1], [First2, Last 2], [First3, Last 3],\r\n[First10, Last 10], [First11, Last 11]]\r\n<\/pre>\n