Search query for Scriptrunner

Question

I Have linked a field in Jira project to my SQL database for users to pick product names by searching through the data base. The search query that I have used is:

SELECT DISTINCT pname as "TOM", pname FROM db.products
WHERE lower(pname) like concat(lower(?), '%')

The query works fine but the issue is in the field it searching match anything starting with typed characters. However, I am looking for a way to search for string regardless of their location. For example if user types: “Head” the search shows: “booklet Head John”.

Mojtaba Mansouri · Answer

Thanks Sayed, I realized following modification would help with my issue:

#Search:
SELECT DISTINCT ID, pname FROM db.products WHERE lower(pname) like concat( '%', lower (?)  , '%')

Sayed Bares [ServiceRocket] · Answer

Welcome to the community Mojtaba!

Something like this should work:

SELECT DISTINCT pname as "TOM", pname FROM db.products
WHERE lower(pname) like "%head%"

Hope it helps!