# Is it possible to check for a number parameter in named query?

**URL:** <https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388>\
**Category:** Ignition\
**Created:** [November 23, 2021, 3:31pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388 "2021-11-23T15:31:40Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 23, 2021, 3:31pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/1 "2021-11-23T15:31:40Z")

</div>

Hello all,

I am trying to check for a value in my named query and that value is an integer parameter.  
The parameter is called **partNo**  
The name query is where I am checking for the word SN and the parameter **partNo** integer value.  
As an example, the serial number is **"SN 1527"**

```auto
SELECT * FROM orderData
WHERE serialNo Like '%SN% :partNo'  

```

I am not able to find the correct syntax for this and I tried so many different syntaxes but none f them worked so far. I would appreciate your help in this regard.

---

<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 23, 2021, 3:42pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/2 "2021-11-23T15:42:26Z")

</div>

I think the issue is the query itself? Best case scenario, you query would be

```auto
SELECT * FROM orderData
WHERE serialNo Like '%SN% 1527'

```

which I’m not sure makes sense. Or maybe I am not well versed enough on the `LIKE` operator, but normally a `%` is used in the beginning or the end to allow any wildcard characters before and after, so to then put a hardcoded value after that seems counter to allowing wildcard. But maybe this is valid.

Does this `SELECT * FROM orderData WHERE serialNo Like '%SN% 1527'` query work correctly if you run it directly?

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 23, 2021, 3:43pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/3 "2021-11-23T15:43:43Z")

</div>

Thank you very much!  
The parameter is dynamic so it will not be always 1527 and the operator change that in a vision view.  
I even tried ‘%SN% 1527’ but it did not return any rows.

---

<div class="post-metadata">

**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\
**Post date:** [November 23, 2021, 3:48pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/4 "2021-11-23T15:48:44Z")

</div>

You can use concat to merge strings

```auto
SELECT * FROM orderData
WHERE serialNo Like concat('%SN%', :partNo)

```

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 23, 2021, 3:53pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/5 "2021-11-23T15:53:55Z")

</div>

Right! concat should work!  
Now I do not get any errors but I still cannot see any rows returned.  
So the complete example is:  
serialNo: SN 1527 Pallet20

I still need to have % for pallet20, right?

```auto
SELECT * FROM orderData
WHERE serialNo Like concat('%SN%', :partNo, '%')

```

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 23, 2021, 3:56pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/6 "2021-11-23T15:56:47Z")

</div>

I just tried the last one and it is working!!! Thank you so much everyone.

---

<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 23, 2021, 3:56pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/7 "2021-11-23T15:56:47Z")

</div>

> [@bkarabinchak.psi](#):
>
> Or maybe I am not well versed enough on the `LIKE` operator, but normally a `%` is used in the beginning or the end to allow any wildcard characters before and after, so to then put a hardcoded value after that seems counter to allowing wildcard. But maybe this is valid.

The `%` character used in this manner is valid. You are saying return any value begining with any combination of characters followed by SN followed by any combination of characters and ending with 1527.

> [@pkhoshroo](#):
>
> So the complete example is:  
> serialNo: SN 1527 Pallet20
> 
> I still need to have % for pallet20, right?
> 
> ```python
> SELECT * FROM orderData
> WHERE serialNo Like concat('%SN%', :partNo, '%')
> 
> ```

That should work so long as you're only concerned with the partNo matching.

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 23, 2021, 3:57pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/8 "2021-11-23T15:57:23Z")

</div>

Thank you so much!!! I just tried that and it worked 🙂

---

<div class="post-metadata">

**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\
**Post date:** [November 23, 2021, 3:59pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/9 "2021-11-23T15:59:19Z")

</div>

> [@pkhoshroo](#):
>
> SN 1527 Pallet20

Be careful with %!  
if your palet also contain number you will get a hit on the numbers in palet too

if you are looking for the number right after SN use \_ or just a space

```auto
SELECT * FROM orderData
WHERE serialNo Like concat('%SN_', :partNo, '%')

SELECT * FROM orderData
WHERE serialNo Like concat('%SN ', :partNo, '%')

```

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 23, 2021, 4:01pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/10 "2021-11-23T16:01:19Z")

</div>

Correct! I used “\_” instead of “%”.  
Thank you, Victor. Appreciate your information.

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 23, 2021, 4:08pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/11 "2021-11-23T16:08:01Z")

</div>

There is just a problem with number 1 only so when the operator checks for partNo=1

it returns:

SN 1 Pallet…  
SN 11 Pallet…  
SN 1527 Pallet…

For this matter, the new script should be:  
(I added a space before the last %)

```auto
SELECT * FROM orderData
WHERE serialNo Like concat('%SN_', :partNo, ' %')

```

---

<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:** [November 23, 2021, 4:11pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/12 "2021-11-23T16:11:04Z")

</div>

Using `LIKE` is not always the best practice for queries. I would make your db serial column hold only the integer and not the `SN` part. Then you would have another column named `pallet`. This will make your life much easier and your queries more efficient and accurate. Then if you want to display it as `SN 1527 Pallet20` you would use

```auto
select concat('SN ', serial, ' Pallet ', pallet) as serialNo
from orderData 
where serial = :partNo

```

---

<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 23, 2021, 5:30pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/13 "2021-11-23T17:30:21Z")

</div>

> [@dkhayes117](#):
>
> Using `LIKE` is not always the best practice for queries.

Hardly ever, I would say. Using LIKE is a big flag that the table doesn't have a good structure.

---

<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 23, 2021, 5:40pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/14 "2021-11-23T17:40:33Z")

</div>

Another option if you cant restructure your table would be to use `SUBSTRING()’

Something like:

```auto
SELECT * FROM orderData
WHERE SUBSTRING(serialNo,4,LEN(:partNo)) = :partNo

```

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 23, 2021, 5:42pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/15 "2021-11-23T17:42:40Z")

</div>

I agree! I will try to adjust the table column in the future if It will be possible. That would make everything easier to interact with. Thank you!!!

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 23, 2021, 5:43pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/16 "2021-11-23T17:43:06Z")

</div>

Good information!! Appreciate it.

---

<div class="post-metadata">

**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\
**Post date:** [November 23, 2021, 5:44pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-check-for-a-number-parameter-in-named-query/53388/17 "2021-11-23T17:44:44Z")

</div>

Great information. I was not aware of this! Thank you, Phill!