public TestDate()
{
fnDbConnect();
try
{
String sql = "SELECT ledate FROM tblTestDate WHERE (ledate REGEXP '^..........$')";
resultSet = st.executeQuery(sql);
while (resultSet.next())
{
String strr = resultSet.getString("ledate");
System.out.println("strr: " + resultSet.getString("ledate"));
}
System.out.println("After");
}
catch(SQLException sqlException)
{
sqlException.printStackTrace();
System.exit(1);
}
fnDbClose();
}
In my table in my database I have values like these: 11/12/1990, 05/08/2001 stored. The regular expression(I used it just for testing purposes) is supposed to give me everything stored.
Error I am getting:
net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: REGEXP
required: )
Yeah, simply use the LIKE operator:
select * from table1 where COLUMN1 like '^[a-zA-Z]#[a-zA-Z] #[a-zA-Z]#$';
(Like in Access, you can use # instead of \d.)
I spent a lot of time to get this hidden feature working.
Here are more examples: myTest
UCanAccess uses HSQLDB as a backing database so we can use HSQLDB's REGEXP_MATCHES() function in UCanAccess queries against Access databases. For example, the following query looks for Canadian postal codes (e.g. 'A1B 2C3') ...
ResultSet rs = stmt.executeQuery(
"SELECT PostalCode FROM Members " +
"WHERE REGEXP_MATCHES(PostalCode, '^[a-zA-Z]\\d[a-zA-Z] \\d[a-zA-Z]\\d$')";
... although, as Marco points out in his answer, UCanAccess also supports Access SQL's regex-like features of the LIKE clause which could be used to accomplish the same thing.
In addition, with UCanAccess we can use the REGEXP_SUBSTRING() function from HSQLDB to actually extract text from a column based on a pattern. The following code extracts the first substring from [TextField] that looks like a North American telephone number (e.g., '416-555-1212' or '403-GOT-BEEF'):
ResultSet rs = stmt.executeQuery(
"SELECT phone " +
"FROM " +
"( " +
"SELECT REGEXP_SUBSTRING(TextField, '\\d{3}-\\w{3}-\\w{4}') AS phone " +
"FROM Table1 " +
") " +
"WHERE phone IS NOT NULL");
