Skip to Content
author's profile photo
Former Member

SAPDB 7.3 - Round brackets in LIKE predicate

Hello!

In SAPDB 7.3 round brackets '(' in LIKE-predicates are not interpreted as any other character but as a regular expression range. That's why there is an InvalidPatternException if you submit a statement like

select * from USER where USERNAME like 'Olf(Matic%'

The exception occurs because SAPDB is missing the closing round bracket. But all I want is to retrieve all Users whose names start with Olf(Matic. Now I know that there is the possibility to escape the round brackets. But as I use a JBOSS with extended finder methods, escaping characters in LIKE predicates is not a solution to me, because JBOSS generates the SQL statement. Furthermore, I want to keep my source code independent of the database used. For examplek, if I escape a round bracket and send the statement to an ORACLE database, I get an exception either, because ORACLE doesn't like "normal" characters to be escaped.

So the question is, is there a possibility to deactivate the feature of defining ranges in LIKE predicates by setting a property or something? As I said, I am using SAPDB 7.3. Upgrading to 7.4 is not really an option to me. I just want SAPDB 7.3 to treat round brackets in LIKE predicates just like any other character there is.

Thanks for any help in advance.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Sep 05, 2005 at 02:51 PM

    Hi Olf,

    indeed, an upgrade to 7.4 should help, but you told us that wasn't really an option.

    It seems the only thing left would be to use the ORACLE SQL mode, there you should get the same behaviour as when connecting to an ORACLE db.

    You can try your command using SQL Studio, but remember to specify the ORACLE mode.

    Hope that helps.

    Regards,

    Roland

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 30, 2005 at 12:19 PM

    Have you tried the 'ESCAPE CHARACTER' syntax?

    i.e. instead of

    SELECT * FROM XYZ WHERE A LIKE 'AA(BB%'

    try something like

    SELECT * FROM XYZ WHERE A LIKE 'AA(BB%' ESCAPE CHARACTER ''

    Regards

    Alexander Schröder

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 30, 2005 at 12:39 PM

    Have you tried the 'ESCAPE CHARACTER' syntax?

    i.e. instead of

    SELECT * FROM XYZ WHERE A LIKE 'AA(BB%'

    try something like

    SELECT * FROM XYZ WHERE A LIKE 'AA(BB%' ESCAPE CHARACTER ''

    Regards

    Alexander Schröder

    Add comment
    10|10000 characters needed characters exceeded