Query logic

Well, to be honest, this looks like a problematic data model design. Is there a specific reason to store a pipe-separated list of values in a single field?

Hi Stefan.

Yeah i agree. Actually its a already existing view and i dont see any specific reason to store this way. We need to change the  design if we dont have option to get the expected result which i posted. Just curious if there is any option to query it.

This is a good example of why, whenever anyone is forced to store an array of data in a text column, i always recommend they at least store it as JSON data.  In that manner there are enough delimiters to allow searching on exact substrings (since you'd be able to search on i.e. the exact string "US" including the quotes, eliminating the accidental inclusion of errant matches like "USA" in this case).  It also takes a lot of the "guesswork" out of parsing the stored contents back into an array (you don't have to worry about writing your own parser and handling edge cases, since JSON takes care of most of that for you).

I guess my question for your case is, do you have alternative options availalbe like writing a View to pre-parse the values a little bit (i think you could probably split multiple values across the [" | "] string, then handle them better, and query from that), or perhaps create a new DB column that could store the same data but parsed into a more readable format?  (Or even a link table where you add an independent entry for each item in the original set?)  Because without a more deluxe solution like that, I'm not sure you'll be able to solve all the corner cases you are likely to run into with your current set-up.

As suggested by Stefan Helzle and Mike Schmitt , you should change the database design if possible. If that option is not available, you can try the following query to get the desired result:

a!queryEntity(
  entity: cons!YD_DT_DEMO,
  query: a!query(
    logicalExpression: a!queryLogicalExpression(
      operator: "OR",
      filters: {
        a!queryFilter(
          field: "name",
          operator: "in",
          value: { "|US", "|US|", "US|" }
        ),
        a!queryFilter(
          field: "name",
          operator: "in",
          value: { "|SP", "|SP|", "SP|" }
        )
      }
    ),
    pagingInfo: a!pagingInfo(startIndex: 1, batchSize: - 1)
  ),
  fetchTotalCount: false
)

Note: You can write code to generate the value of in parameters { "|US", "|US|", "US|" }.

Sorry, but the reason to change the design is NOT the query problem. The reason is, that it is against any best practices, and violating these creates the issue you are facing.