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!