ODBC - Parsing query with % character

[Wei Wang]

Wanted to report an ODBC parsing issue related to the special character “%”. When we have a query with this character, the query does not parse properly and I see an error in DevTools stating “unable to decode value” for the query field. % is used commonly in conjunction with LIKE so it would be great if the parsing logic can account for it. Thanks!

‌

[Scott Wiltshire]

Absolutely! Thanks for reporting. I can hardly think of a case where I wouldn't use LIKE and the % wildcard operator in a SQL query.

[Ilia Shapiro]

please provide the query that is used

[Wei Wang]

something like this for example doesn’t work:

SELECT accounting_month 
FROM table_name  
WHERE accounting_month LIKE '2024-01-%' 
limit 10;

whereas this does

SELECT accounting_month
FROM table_name 
WHERE accounting_month = '2024-01-01'
limit 10;

‌

[Ilia Shapiro]

please use the following

SELECT accounting_month 
FROM table_name  
WHERE accounting_month LIKE '2024-01-' + CHAR(37) 
limit 10;

37 is the decimal ASCII code for a percent symbol

[Wei Wang]

java.sql.SQLException: [Redshift][ODBC Driver][Server]42601:ERROR:  syntax error at or near "CHR"
 LINE 1, POSITION 50: ..._summary WHERE accounting_month LIKE '2024-01-'   CHR(37) li...

Thanks for the suggestion Ilia! We are querying a RedShift table so CHR would be the equivalent of CHAR (which works in DataGrip). However, when I try either CHAR or CHR in Apliqo, I see the error above

[Ilia Shapiro]

Fix for now use ‘%25' to encode '%’ in the next release the query string will be encoded to automatically perform the encoding.

[Wei Wang]

thanks for the workaround Illa! confirmed that using “%25” does indeed work!

[Wei Wang]

just tested in v2024.02 FP1 and confirmed that % works fine now (w/o using %25). Thanks!

[Joana MFC Corte-Real]

Dear Wei, Thank you for your feedback. I will mark this as resolved. Please, do open another ticket if the issue reappears.