SAPDB 7.3 - Round brackets in LIKE predicate

[Former Member]

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.

[alexander_schroeder]

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

[alexander_schroeder]

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

[roland_mallmann]

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