Check if an integer value inside a list of strings using Firebird

Question

I am writing a stored procedure and I need to check if the integer value of the variable is inside the list.

I tried like this

If (var_int1 in (4,6,9) ) then .... 

This work, but the list of numbers will return to me from the varchar field, so there is a way to use IN without using a temporary table.

Answer 1

You can use the built-in position function:

 if(position(val IN valList) > 0)then 

Note that you need to avoid false positives when only part of the number matches, i.e. setpoints '123,456,789' for position '2' return true, which you probably don't want. To avoid this, you can add a comma (since you divided the comma of the value in the varchar field) as a prefix and suffix into the search strings, i.e.

 val = ',' || cast(var_int1 as varchar(10)) || ','; valList = ',' || valList || ','; if(position(val IN valList) > 0)then ... 

Answer 2

Just to provide another possible solution:

 valList = '2,3,4'; --- varchar val = 3; --- integer if ( valList like '%'||val||'%' ) then ...