# Pass Wildcard Parameters on Named Queries

**URL:** <https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594>\
**Category:** Ignition\
**Tags:** namedqueries, ignition79, ignition80, database, scripting\
**Created:** [May 8, 2020, 11:09pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594 "2020-05-08T23:09:54Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![lloyd.tripp](https://avatars.discourse-cdn.com/v4/letter/l/a88e4f/32.png) [@lloyd.tripp](https://forum.inductiveautomation.com/u/lloyd.tripp)\
**Post date:** [May 8, 2020, 11:09pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/1 "2020-05-08T23:09:54Z")

</div>

Hello Everybody!,

I’d like to setup a Named Query that selects rows based on WHERE conditions. In some use cases, I’d like to pass a wildcard (\*) for a parameter. So far, it seems like the only solution is to create a new Named Query with an omitted WHERE condition.

What do you guys think?

---

<div class="post-metadata">

**Author:** ![bmusson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmusson/32/87530_2.png) [@bmusson](https://forum.inductiveautomation.com/u/bmusson)\
**Post date:** [May 9, 2020, 1:09am UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/2 "2020-05-09T01:09:42Z")

</div>

Sounds like you could utilize the SQL ‘LIKE’ operator.

[https://www.w3schools.com/sql/sql\_like.asp](https://www.w3schools.com/sql/sql_like.asp)

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [May 9, 2020, 11:11pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/3 "2020-05-09T23:11:58Z")

</div>

What is your query? and where do you want this wildcard?

---

<div class="post-metadata">

**Author:** ![lloyd.tripp](https://avatars.discourse-cdn.com/v4/letter/l/a88e4f/32.png) [@lloyd.tripp](https://forum.inductiveautomation.com/u/lloyd.tripp)\
**Post date:** [May 10, 2020, 6:59pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/4 "2020-05-10T18:59:49Z")

</div>

Query looks like this:

"SELECT \* FROM table  
WHERE  
Column1 = :String1  
AND Column2 = :Int1  
AND Column3 = :Int2  
"

Query works great if I run system.db.runNamedQuery, but in some cases I’d like to grab rows that only match the first two arguments and pass a wildcard integer for :Int2 .

How would I put a wildcard in the python dictionary or should I add ‘LIKE’ operator like @bmusson said?

Thanks guys

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [May 10, 2020, 9:48pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/5 "2020-05-10T21:48:53Z")

</div>

You can’t use `LIKE` in your instance.

You can use this :

```auto
SELECT * FROM table
WHERE
Column1 = :String1
AND Column2 = :Int1
AND (CAST(Column3 as nvarchar) = :Int2 OR :Int2 = '*' ) 

```

Note: your “int2” would actually need to be a string type parameter, that, or add a new parameter called `Int2Enable`

```auto
SELECT * FROM table
WHERE
Column1 = :String1
AND Column2 = :Int1
AND (Column3 = :Int2 OR :Int2Enable <> 1 ) 

```

You could use a query parameter instead, but then you would rely on people supplying the right string (mentioned this just for other ideas, definitely not the best option though) .

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [May 10, 2020, 11:00pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/6 "2020-05-10T23:00:27Z")

</div>

LIKE doesn’t work for integer columns, only for string columns. I would make another named query. IA isn’t charging you for extra ones, after all. 😄

---

<div class="post-metadata">

**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\
**Post date:** [May 11, 2020, 12:42am UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/7 "2020-05-11T00:42:33Z")

</div>

Something that I do quite frequently is to add a 1 = Case statement like the following, allows you to just pass a null if you want to “ignore” the check for a certain column

```auto
SELECT * FROM table WHERE 
1 = (CASE WHEN :Int1 IS NULL THEN 1 WHEN :Int1 = Column1 THEN 1 ELSE 0 END) AND
1 = (CASE WHEN :Int2 IS NULL THEN 1 WHEN :Int2 = Column2 THEN 1 ELSE 0 END) AND
1 = (CASE WHEN :String1 IS NULL THEN 1 WHEN :String1 = Column3 THEN 1 ELSE 0 END)

```

In this case you might pass it the following to ignore the Column2 check  
Int1 = 1, Int2 = NULL, String1 = “Test”

You can change the **WHEN :Int1 IS NULL THEN 1** To whatever you like to ignore that parameter, If null is a valid parameter in this case then maybe you use **WHEN :Int1 = -1 THEN 1**

Note, I also typically put these in stored procedures, as I find keeping as much SQL code on the SQL server as possible is easier to manage, so I am really just executing the named query as

```auto
EXEC dbo.StoredProcedure :Int1, :Int2, :String1

```

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [May 11, 2020, 1:42am UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/8 "2020-05-11T01:42:24Z")

</div>

Note: you won’t be able to use NULL in a named query, unless maybe you’re using the script function and can actually pass in nulls in (not sure if possible). You can’t pass nulls into a binding though

---

<div class="post-metadata">

**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\
**Post date:** [May 11, 2020, 1:58am UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/9 "2020-05-11T01:58:19Z")

</div>

> [@nminchin](#):
>
> you won’t be able to use NULL in a named query

Good point @nminchin

I forgot to mention this, I typically use scripting for named query calls, however to use the property bindings you would need to use something like a -1 or empty string "" to accomplish this.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [November 30, 2021, 8:29pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/10 "2021-11-30T20:29:37Z")

</div>

I read through twice.  
I don’t see the solution for the binding to passing a wildcard into a named query other than just having two different queries, which is a fine option for me.

I made a named query. I want to check for either a specific stopCode or else to get all of them.  
So in my where clause I have

`and stopCode = :thisCode `

I tried to pass a star, and that did not work.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [November 30, 2021, 8:57pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/11 "2021-11-30T20:57:25Z")

</div>

There is no wild card for an Integer. An integer is either an exact match or it isn’t.

However, you can easily write a query that has multiple execution paths depending on if a particular stopCode is present or not using the EXISTS operator.

```auto
IF EXISTS(SELECT 1 FROM yourTable WHERE stopCode = :thisCode)
BEGIN
    SELECT * FROM yourTable WHERE stopCode = :thisCode
END
ELSE
BEGIN
    SELECT * FROM yourTable
END

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 30, 2021, 9:02pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/12 "2021-11-30T21:02:33Z")

</div>

> [@lrose](#):
>
> multiple execution paths

Bleh. So a legitimate code that just hasn't happened since the last DB partition pruning blows up your result set.....

I recommend an OR in the where clause:

```auto
and (:thisCode = -1 OR stopCode = :thisCode)

```

Smart DBs might even factor out that comparison when given the -1.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [November 30, 2021, 9:03pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/13 "2021-11-30T21:03:48Z")

</div>

> [@pturmel](#):
>
> `and (:thisCode = -1 OR stopCode = :thisCode)`

+1 for this, I use this a lot in my named queries, its the "cleanest" way to do conditional where clauses in a Named Query.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [November 30, 2021, 9:05pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/14 "2021-11-30T21:05:05Z")

</div>

> [@pturmel](#):
>
> I recommend an OR in the where clause:
> 
> ```auto
> and (:thisCode = -1 OR stopCode = :thisCode)
> 
> ```
> 
> Smart DBs might even factor out that comparison when given the -1.

huh. never thought of that.

---

<div class="post-metadata">

**Author:** ![Stef\_Velers](https://avatars.discourse-cdn.com/v4/letter/s/bc8723/32.png) [@Stef\_Velers](https://forum.inductiveautomation.com/u/Stef_Velers)\
**Post date:** [January 10, 2024, 3:57pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/15 "2024-01-10T15:57:09Z")

</div>

Hi,  
Is there a way you can use the LIKE function and % wildcard with also a parameter to filter a string colum?  
I tried many things but did not manage to get it working.  
My code:

```auto
SELECT
DATETIME(ROUND(ae.event_timestamp/ 1000), 'unixepoch') AS Time,
ae.actor AS User,
case when ae.action='tag write' then
(select oae.action_value from AUDIT_EVENTS oae where oae.action=ae.action and oae.action_target=ae.action_target and oae.EVENT_TIMESTAMP<ae.EVENT_TIMESTAMP order by oae.EVENT_TIMESTAMP desc LIMIT 1)
else '' end as oldValue,
ae.action_value AS NewValue,
ae.action_target AS Target
FROM AUDIT_EVENTS ae
WHERE ae.action_target = :TargetFilter LIKE '%' and ae.event_timestamp > :Startdate and ae.event_timestamp < :Enddate and ae.action = 'tag write'

```

I want to filter action\_target with a string parameter. I have a text field in my window where I can type a string and what I want is that the wildcard is placed at the end of the string.  
The target colum has to show the data that starts with the text that is in the parameter and all the wildcard fills all that comes after that.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/d/adf0566b9e85c785e6a1fea2473e0de4db66fd06.png)  
If I type "[Test SCADA Ignition]" in my text field where the parameter is bind to the wildcard has to fill the rest.

anyone that knows what I'm doing wrong or if this is even possible

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [January 10, 2024, 4:03pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/16 "2024-01-10T16:03:30Z")

</div>

if you want to use a parameter in some fashion with the `LIKE` operator, you concatenate it.

```auto
select * from table
where col like concat(:param,'%') 

```

---

<div class="post-metadata">

**Author:** ![Stef\_Velers](https://avatars.discourse-cdn.com/v4/letter/s/bc8723/32.png) [@Stef\_Velers](https://forum.inductiveautomation.com/u/Stef_Velers)\
**Post date:** [January 10, 2024, 4:09pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/17 "2024-01-10T16:09:48Z")

</div>

> [@dkhayes117](#):
>
> `concat(:param,'%') `

It doesn't recognise the function CONCAT

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [January 10, 2024, 4:10pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/18 "2024-01-10T16:10:41Z")

</div>

use the function that is available in your DB flavor

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [January 10, 2024, 4:10pm UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/19 "2024-01-10T16:10:59Z")

</div>

> [@Stef\_Velers](#):
>
> `ae.action_target = :TargetFilter LIKE '%'`

This, itself, is not valid SQL syntax. When using `LIKE`, you don't use an equals sign.

---

<div class="post-metadata">

**Author:** ![Stef\_Velers](https://avatars.discourse-cdn.com/v4/letter/s/bc8723/32.png) [@Stef\_Velers](https://forum.inductiveautomation.com/u/Stef_Velers)\
**Post date:** [January 12, 2024, 7:16am UTC](https://forum.inductiveautomation.com/t/pass-wildcard-parameters-on-named-queries/34594/20 "2024-01-12T07:16:20Z")

</div>

ah Okay I will try.

Edit:

> [@dkhayes117](#):
>
> if you want to use a parameter in some fashion with the `LIKE` operator, you concatenate it.
> 
> ```python
> select * from table
> where col like concat(:param,'%') 
> 
> ```

I got it working!  
I searched for my DB flavor and got it working with the following:

```python
ae.action_target LIKE :TargetFilter || '%'

```