Skip to main content
L
linp0001
August 18, 2021
Question

How to build a query with the filter similar as SQL LIKE

  • August 18, 2021
  • 5 replies
  • 0 views

Hi there,

In the SQL, we can use LIKE as the filter, but in the Appian, there is no such "operator". so in my case, I am trying to build a query to search a database by people's name.

if using operator "includes" or "starts with" to search value "Michael", then it will return list of people, but if search value "Michael S", it will return nothing, but it should return some.

in the SQL, we can use LIKE 'Michael S%', but dont know how to implement this in the Appian.

Could anyone here can help to point a direction?

Best regards,

Lin

5 replies

joshl
joshl
August 18, 2021

A couple of ideas. Not really in recommended or "performant" order as it would depend on your exact use case, data loads, etc.

  • Use the Query Database smart service to write out your query and use the SQL LIKE functionality
  • Pull back a set of primary keys and names into a local variable, use something like the Regex Plugin to find matches in the names, then re-query for the full dataset with the matching primary keys
  • Write a stored procedure that mimics a query using the SQL LIKE functionality and invoke it using this plugin
L
linp0001Author
August 18, 2021

thanks.

csteward
csteward
August 18, 2021

To note, I wouldn't expect to have any issues with your use case.

The Appian "includes" operator for "Michael S" should operate as SQL does with LIKE '%Michael S%'.  The "starts with" operator should perform exactly as your SQL LIKE 'Michael S%' call.

This is an example where we can search for "includes" value "ware deve" and it correctly returns any user with job title similar to "Software Developer".

Can you share your code?

a!queryEntity(
  entity: cons!USER_DS,
  query: a!query(
    paginginfo: a!pagingInfo(1,10),
    filter: a!queryFilter(
      field: "jobTitle",
      operator: "includes",
      value: "ware deve"
    )
  )
)

L
linp0001Author
August 18, 2021

Thanks Chris, I got it.

I guess when I tried it, I actually did not use "Michael" (this is just what I put it there, did not want to reveal my coworker's name). I actually used "Chris A" for testing, now found out his actual name is "Christopher A.." in the database, but I did not know, we usually just call him Chris, so that's the reason it did not work.

Thank you very much for the message.

regards,

Lin

csteward
csteward
August 18, 2021

Great!

Feel free to mark the thread answered if you are good to go! :) 

Terms & ConditionsAccessibility statement