In my web application the following abbreviation allows me to input a value and choose its type within the application e.g. :input
In this case I created a variable :grades
as a varchar (You will understand why I chose this as varchar)
The teacher wants to know who received a grade of 2 or 3, maybe more
e.g. 2,3 -- She wants to know who got a grade of 2 and who got a grade of 3
So I produced the following code to just do that!
where gc.idx in (cast(regexp_split_to_table(:grades, ',')) as integer)
This obviously didn't work and I assume that's because this splits into a table and not into a string. How can I make this expression work?
Don't split into a table, convert it into an array and use the = any
operator with an array.
where gc.idx = any (string_to_array(:grades, ',')::int[])
If for some strange reason your "web app" doesn't allow the ::int[]
notation, you can use a cast()
expression:
where gc.idx = any (cast(string_to_array(:grades, ',') as int[])