Regular expression when querying an Access database using UCanAccess

Question

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 there are such values: 11/12/1990, 05/08/2001. The regular expression (I used it for testing purposes only) should give me everything saved.

The error I am getting is:

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: REGEXP required :)

Answer 1

UCanAccess uses HSQLDB as a support database, so we can use the HSQLDB REGEXP_MATCHES() function in UCanAccess queries for Access databases. For example, the following query searches for Canadian postal codes (for example, "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 SQL Access functions, like regular expressions, in a LIKE expression that can 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 template. The following code retrieves the first substring from [TextField], which looks like the North American phone number (for example, "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"); 

Answer 2

Yes, just use the LIKE statement:

 select * from table1 where COLUMN1 like '^[a-zA-Z]#[a-zA-Z] #[a-zA-Z]#$'; 

(As in Access, you can use # instead of \d .)

I spent a lot of time to make this hidden function work.

Here are some more examples: myTest