Hi Experts,
Environment: Using PowerBuilder 2022 Build 1900 (SQL Server 2019)
I am coding a smart search (but stuck) on a data window filter:
I have a window with top simple search datawindow (just like single line edit where i am entering the text) and bottom portion i have a results grid datawindow (displaying the results).
The bottom results datawindow displays columns like → Name, File Number, Location, etc...
I want to enter search like any special character (~, `, !, @, #, $ etc) and display results that match any of the columns with any matched characters.
I have trouble filtering single quotes and double quotes (rest all special characters are working fine).
► I want to keep building the expression: ◄
ls_searchtext = entered a single quote
►/*filter by name */
ls_Filter = "( lower(name) like '"+ ls_searchtext +"'" + ")"
li_Filter = dw_results.Filter()
ls_current_filter = dw_results.object.datawindow.table.filter
►/*filter by file number*/
ls_Filter = ls_current_filter + " OR ( " + "lower(file_number) like '"+ ls_searchtext +"'" + " )"
li_Filter = dw_results.Filter()
►/*filter by location*/
ls_current_filter = dw_results.object.datawindow.table.filter
ls_Filter = ls_current_filter + " OR ( " + "lower(location) like '"+ ls_searchtext +"'" + " )"
li_Filter = dw_results.Filter()
► more columns.
The issue is with using single quotes and double quotes i get error message → "Missing close paranthesis"
how do i search in any of the columns value with a single quote or double quotes using filter expression.
For example → I want to search for a word called → "it's" and filter results that matches → "it's" or could be a name like "Mc' Arthur"
What is the additional escape characters that i have to use for single quotes or double quotes while filtering results (i have tried ~~~' and other combinations like \' but no luck). Is there any other smarter way to do this or code examples.
Any help greatly appreciated.
Thanks in Advance
Suresh
What worked for me as a solution for the issue of searching a string with single or double quotes was to replace single tilde with 2 tildes in the script:
1. I had to replace single tilde → ~ with 2 tildes → ~~
ls_searchtext = lower(lnv_string.of_trim(lnv_string.of_globalreplace(ls_searchtext, "'", "~~'"))) /*single quotes*/
ls_searchtext = lower(lnv_string.of_trim(lnv_string.of_globalreplace (ls_searchtext,'"', '~~"'))) /*double quotes*/
2. I had to remove this line as well as this was causing the extra grief of "missing closing parenthesis"
► ls_current_filter = dw_results.object.datawindow.table.filter
Instead i took the last filter that was applied
► ls_current_filter = "( lower(trim(name)) like '"+ ls_searchtext +"'" + ")" + &
" OR ( " + "lower(trim(file_number)) like '"+ ls_searchtext +"'" + " )"
HTH someone :)
Suresh