on 08-24-2005 2:20 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.