executeSQLQuery - wildcards with +e164 extensions

[gcw]

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...

Regards
Gavin

[dstaudt]

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. 

http://www-01.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.sqls.doc/ids_sqs_1041.htm?lang=en

What is the reason for the double backslash before the '+'?

[gcw]

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..

Any other ideas?

[gcw]

Thanks dstaudt 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*'

-or-

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*..."