Skip to Content
author's profile photo Former Member
0
Former Member

SAPDB 7.3 - Round brackets in LIKE predicate

Posted on Aug 24, 2005 at 01:20 PM 16 Views

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 a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Roland Mallmann
    Roland Mallmann
    Posted on Sep 05, 2005 at 02:51 PM
    0

    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 a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Alexander Schroeder
    Alexander Schroeder
    Posted on Sep 30, 2005 at 12:19 PM
    0

    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 a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Alexander Schroeder
    Alexander Schroeder
    Posted on Sep 30, 2005 at 12:39 PM
    0

    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 a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.