I am using the below prompt in a report and I want to be able to have the % wildcard automatically concatenated around each word in the search criteria. For example if a user wants to search for SMITH,JONES, I want the prompt to put in the % wildcard so it looks like this: Search Criteria: %SMITH%,%JONES%
I was able to concatenate the % when just using LIKE but with LIKE ANY, I couldn't get it to work.
fieldname LIKE ANY (#csv( split(',', toupper(prompt('SEARCH_CRITERIA', 'token'))))#)
The version of Cognos I am using is: IBM Cognos Analytics with Watson 11.2.4
Thank you
You say you got it to work with LIKE. What expression did you use with LIKE?
This is what i used for LIKE:
fieldname LIKE UPPER('%'||(#sq(prompt('SEARCH_CRITERIA', 'token'))#)||'%')
I use SQL Server, so I can't try LIKE ANY, but will this work?
fieldname LIKE ANY (#csv(substitute('$', '%', substitute('^', '%', split(',', toupper(prompt('SEARCH_CRITERIA', 'token'))))))#)
Get SEARCH_CRITERIA value.
Convert the value to upper case.
Split comma-delimited string into individual values.
Replace the beginning (0 characters) of each string with %.
Replace the ending (0 characters) of each string with %.
Join the individual strings into a comma-delimited list.
You may need to wrap the csv() function in sq()
dougp, thank you so much. your suggestion worked perfectly!