How do I set up partial text matching for Parameters?

Aarthi
Aarthi
  • Updated

Let’s say you want to display results where a parameter value is partially matched. You can use LIKE and CONCAT together in order to display the values where the parameter input partially or fully matches the table value.

# registry_entity table
Name
1. Myosin 1
2. Myosin 2
3. Test Entity 1
4. Test Entity 2
5. Myosin 3

# SQL query
SELECT re.Name as "Name"
from alab.registry_entity$raw re
WHERE (#{{Name}} IS NULL) OR (Name LIKE CONCAT('%', #{{Name}}, '%'))

# Parameter
Name Myo

# Output
1. Myosin 1
2. Myosin 2
3. Myosin 3

Now, let’s say you want the parameter to accept case insensitive values and still display the correct results. You can use ILIKE instead of LIKE.

# SQL query
SELECT re.Name as "Name"
from alab.registry_entity$raw re
WHERE (#{{Name}} IS NULL) OR
(Name ILIKE CONCAT('%', #{{Name}}, '%'))

# Parameter
Name myo

# Output
1. Myosin 1
2. Myosin 2
3. Myosin 3

Was this article helpful?

Have more questions? Submit a request