informix sql select statement manipulate string argument
Posted on 2010-09-15
Working with a proprietary reporting tool connected to an ERP system.
The reporting tool allows for a user prompt (called a user field in this case with the name myvariable)
We want the user to be able to type in the following into the user field prompt:
The reporting tool uses the user field myvariable as follows
SELECT * FROM mytable WHERE fielda IN(@myvariable)
The reporting tool produces the following sql which does not work because
of missing single quotes.
SELECT * FROM mytable WHERE fielda IN('16,17,18,20,21')
The SQL needs to be this in order to work
SELECT * FROM mytable WHERE fielda IN('16','17','18','20','21')
Is there a sql string manipulation function(s) that can translate or transform the user field value (without quotes) in to the final SQL with quotes around each value?