Use String parameter for RegEx in query

Question

In my query (the database is a sql server) I use a RegEx for a select command like this:

SELECT * FROM test WHERE id LIKE '1[2,3]'

(This query is tested and returns the data I want)

---

I want to use a paramter for this RegEx. For that I definded the Paramter in iReport $P{id} as a string and the value is "1[2,3]".

In my query I use now this parameter like this:

SELECT * FROM test WHERE id LIKE $P{id}

As result I get a blank page. I think the problem is that the value of the parameter is defined with " ". But with ' ' I get a compiler error that the paramter isn't a string.

I hope someone can help me.

Answer 1

LIKE applies to text values, not to numeric values. Since id is numeric use something like this:

SELECT * FROM test WHERE id IN (12, 13)

with the parameter

SELECT * FROM test WHERE id IN ($P!{id_list})

and supply a comma separated list of ids for the parameter. The bang (!) makes sure that the parameter will be inserted as-is, without string delimiters.

Btw: LIKE (Transact-SQL) uses wildcards, not regex.

You can still use LIKE since there exists an implicit conversion from numeric types to text in T-SQL, but this will result in a (table or index) scan, where as the IN clause can take advantage of indexes.

Answer 2

The accepted answer works but it is using String replacement, read more about sql-injection, to understand why this is not good practice.

The correct way to execute this IN query in jasper report (using prepared statement) is:

SELECT * FROM test WHERE $X{IN, id, id_list}

For more information as the use of NOTIN, BETWEEN ecc. see JasperReports sample reference for query