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