Regular Expression matching when querying an Acces

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 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: )

Answer 1

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

Answer 2

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");