When trying to run executeSQLQuery such as this:
select numplan.pkid from numplan where dnorpattern like '\\+1123456';
this returns a value
but if I wish to use a wildcard:
select numplan.pkid from numplan where dnorpattern like '\\+112345%';
it returns nothing...
it seems that the escape character is altering the result as this does work:
select numplan.pkid from numplan where dnorpattern like '%+112345%';
Does anyone know what is going wrong here? of course I could just replace \\ with % at the beginning and be done with it, but I'd like to know if it is a bug or a feature...
The SQL statement is passed essentially straight through to the underlying database, IBM Informix Dynamic Server. Per the associated documentation, it looks like '\' is needed only to escape a '%' itself.
What is the reason for the double backslash before the '+'?
Thanks for the reply, I also read this - "You can also use an escape character to escape itself"
in the database +E164 numbers are stored in the 'numplan' table with the escape character '\' preceding the '+'
- so the escape character is stored in the entry of the table
- '+' is not an escape character in SQL but '\' is so in order to query for the string '\+12345' in SQL I would need to query '\\+12345'
This does work for a direct entry - ie, a number is found if '\+12345' is there, however if I put '\\+1234%' or '\\+1234_' then that same number is not found in that query, I don't wish to escape the '%' I wish to use it as a wildcard character, there appears to be a limitation when using a wildcard with an escape character on the same query..
Thanks for pointing me off to find the comprehensive manual "IBM Informix Guide to SQL: Syntax" - its a ripper.
the chapter titled - LIKE and MATCHES Condition gives a few solutions..
dnorpattern matches '[\]+618644431*'
dnorpattern like 'z\+618644431%' ESCAPE 'z';
No real clue as to why my first option did not work, it should probably say "You can also use an escape character to escape itself - *as long as you dont use the default escape character with a wildcard as well*..."