Finding records containing '#' character

[Kevin T]

Hi,

I would like to find records in an .accdb file that contain the '#' character.

I tried this query which did not return any:

SELECT * FROM TestCase1 WHERE `Discrepancy` LIKE '%#%'

After reading:
http://www.hsqldb.org/doc/guide/ch09.html
http://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions016.htm

I tried this query, which failed:

SELECT * FROM TestCase1 WHERE `Discrepancy` like '%\#%' ESCAPE '\'

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.2 unexpected token: ESCAPE
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:211)
    at arffconverter.FilterEvaluator.evaluateSingle(FilterEvaluator.java:1811)
    at arffconverter.FilterEvaluator.main(FilterEvaluator.java:2112)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ESCAPE
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208)
    ... 2 more
Caused by: org.hsqldb.HsqlException: unexpected token: ESCAPE
    at org.hsqldb.error.Error.parseError(Unknown Source)
    at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 5 more

I know the ESCAPE clause works because this query returned records containing the underscore character:

SELECT * FROM TestCase1 WHERE `Discrepancy` LIKE '%\_%' ESCAPE '\'

I'm relatively new to this so I'm guessing I'm missing something. Any help would be much appreciated.

Thanks,
Kevin

[Gord Thompson]

I'm not 100% sure about escaping special characters in the LIKE clause of a UCanAccess query, but I just tested this as a workaround and it does seem to do the trick:

sql = "SELECT * FROM TestCase1 WHERE InStr([Discrepancy],'#')>0";

 
(InStr() is one of the [many] Access SQL functions that UCanAccess supports.)

[Kevin T]

Got it. I tried the query:

sql = "SELECT * FROM TestCase1 WHERE InStr([Discrepancy],'#')>0"

and I'm afraid it failed for me:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.2 Java execution: INSTRWA
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:211)
    at arffconverter.FilterEvaluator.evaluateSingle(FilterEvaluator.java:1671)
    at arffconverter.FilterEvaluator.main(FilterEvaluator.java:1975)
Caused by: java.sql.SQLException: Java execution: INSTRWA
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208)
    ... 2 more
Caused by: org.hsqldb.HsqlException: Java execution: INSTRWA
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.Routine.invokeJavaMethod(Unknown Source)
    at org.hsqldb.Routine.invoke(Unknown Source)
    at org.hsqldb.FunctionSQLInvoked.getValueInternal(Unknown Source)
    at org.hsqldb.FunctionSQLInvoked.getValue(Unknown Source)
    at org.hsqldb.ExpressionLogical.getValue(Unknown Source)
    at org.hsqldb.Expression.testCondition(Unknown Source)
    at org.hsqldb.RangeVariable$RangeIteratorMain.findNext(Unknown Source)
    at org.hsqldb.RangeVariable$RangeIteratorMain.next(Unknown Source)
    at org.hsqldb.QuerySpecification.buildResult(Unknown Source)
    at org.hsqldb.QuerySpecification.getSingleResult(Unknown Source)
    at org.hsqldb.QuerySpecification.getResult(Unknown Source)
    at org.hsqldb.StatementQuery.getResult(Unknown Source)
    at org.hsqldb.StatementDMQL.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 5 more
Caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    ... 21 more
Caused by: java.lang.NullPointerException
    at net.ucanaccess.converters.Functions.instr(Functions.java:819)
    at net.ucanaccess.converters.Functions.instr(Functions.java:830)
    ... 24 more

[Gord Thompson]

That's strange, because I tried it and it worked for me when [Discrepancy] was either a Text(255) or a Memo field. Can you post a small sample database that recreates the issue?

[Marco Amadei]

As far as InStr is concerned, it means some null values are in Discrepancy column. so you may want to use SELECT * FROM TestCase1 WHERE InStr(nvl([Discrepancy],' '),'#')>0 instead.
I'm analyzing the issue with the # special character(which means digit in access), there may be a bug. However we are in time for the 3.0.3.

[Marco Amadei]

Okay, forget about hsql syntax. In general special character should be escaped with square brecket as well as in access. Unfortunately there was a specific "sharp" bug so the workaround Gord suggested (with nvl) is the only solution till the 3.0.2. Next week, with the 3.0.3 you'll be able to use
SELECT * FROM TestCase 1 WHERE Discrepancy LIKE '%[#]%'

[Marco Amadei]

Fixed in svn trunk.

[Kevin T]

The workaround with nvl worked, as well as the square brackets in the latest version 3.0.3. Just left a 5-star review. Thank you Gord and Marco!

Kevin